Authenticate User using ENVIRON("username")

[Nick Bardury]

Good morning. First time poster, long time reader.

Firstly, apologies if this isn't in the right place, but this seemed the most appropriate.

Secondly, I require a little help. I have an Access database (v2003) that we have used for many years, which has a lot of data in it. It is currently used only in my department by trusted individuals and is therefore unsecured. I am going to allow other departments access

What I would like to know is: When the database starts, how do I get the database to grab the username, check it against a table to see if it matches a value in the table, if it does then display a welcome message. If it doesn't match for it to say no access allowed.

I remember before the hacking of a few weeks ago there was some information about this, but I can't seem to find anything relating to it this morning.

Many thanks.

Nick

 

[reclusivemonkey]

Hi Nick,

Are you asking for the code? You are right in that ENVIRON("username") will get the user name, so all you need to do is check this value against a table of users, and then close the DB if you don't get a match. It seems you already know what to do here?

 

[Nick Bardury]

I know(ish) the concept of what to do, I just don't know how to accomplish it. My coding skills are pretty limited at best. I can usually work my way through code and work out what it does and adapt it.

So I suppose, yes I was looking for an example of how it's done, as I'm sure there used to be one in the Sample Database section before the hacking episode.

 

[reclusivemonkey]

Sub CheckValidUser()
    
    Dim strUser As String
    strUser = Environ("UserName")
    Select Case strUser
        Case "UserA"
            MsgBox "Valid User"
        Case Else
            Application.CloseCurrentDatabase
    End Select

End Sub

Down and dirty, but I charge £50 an hour for IT work, so thats what you get for free ;-)

I used a select case rather than linking to a table as it makes for a quicker example. Providing you don't have that many users, this would work fine. To get this to run on open, I would attach it to a form, call it on "Open Form", and set the DB to open this form on Startup. I don't *think* there is an Autpen in Access as there is in Excel.

 

[namliam]

Actually monkey,

If you create a Macro called Autoexec it will be executed "on open" post haste (or something LOL) when you open the database

But opening the form (and possibly keeping it open, but hidden in the background) also works and can give certain distinct advantages.
e.g. you could write the username or certain Initial settings to that form and fetch them when needed from there, instead of fetching them from elsewhere.

Most of us charge by the hour I guess, so this is your "freebee" ;-)

 

[reclusivemonkey]

If you create a Macro called Autoexec it will be executed "on open" post haste (or something LOL) when you open the database

[...]

Most of us charge by the hour I guess, so this is your "freebee" ;-)

LOL! Thanks namliam, thats useful to know.

 

[modest]

I used a select case rather than linking to a table as it makes for a quicker example. Providing you don't have that many users, this would work fine. To get this to run on open, I would attach it to a form, call it on "Open Form", and set the DB to open this form on Startup. I don't *think* there is an Autpen in Access as there is in Excel.

As he mentioned this is a short way, but if you have a table:

Sub CheckValidUser()
    
    Dim strUser As String
    strUser = Environ("UserName")
    Select Case dlookup("[Field Name]","Table Name","[Field Name]='" & strUser & "'") & ""
        Case strUser
            'Do welcome message
            MsgBox "User Authenticated"
        Case ""
            MsgBox "User Not Authenticated"
            Application.CloseCurrentDatabase
    End Select

End Sub

Although namliam pointed out you can use a macro on auto-open, I still use a form. Because you will soon see that you're going to need more then getting the Environ("UserName"). For instance, if someone logs in on their computer, but someone else comes up and opens the Database file, they will be able to use it. Hence I use a Login/Logout form.

But, if you do a little searching, there is a difficult-to-use security ability provided within Access.