Checking user's credentials

[IgorM]

Hi

I build an app in Excel where user is obligated to enter a user name and
password. If the given user name with password exists in an Access database
the application will start.
But how to check if the given user (with such password) exists in the mdb
file? I wouldn't like to try to run a random query to see if it returns
results. I was rather thinking about something more advanced - a solution
that checks only the user name and password. In VBA of course.


Kind regards
IgorM

 

[John W. Vinson]

Hi

I build an app in Excel where user is obligated to enter a user name and
password. If the given user name with password exists in an Access database
the application will start.
But how to check if the given user (with such password) exists in the mdb
file? I wouldn't like to try to run a random query to see if it returns
results. I was rather thinking about something more advanced - a solution
that checks only the user name and password. In VBA of course.


Kind regards
IgorM

So use a query with the desired username and password as parameters. That's
hardly a "random query". You may also be able to use the DLookUp function.

 

[IgorM]

Ok. But what if I want to check the group which the user belongs to?

 

[John W. Vinson]

Ok. But what if I want to check the group which the user belongs to?

Well, include that in the query.

I'm groping in the dark here, Igor, because you haven't posted anything about
the structure of your tables, where the group and user information is stored,
etc. Is this Access Workgroup security, or something you've implemented
yourself?

 

[IgorM]

Appologies for not being too specific. I set a password for the Admin
account in the database. So when I try to open the file in Windows Explorer
a userform pops up asking for user name and password. if I don't provide
them it shuts down the database. So when user types her user name and
password I'd like to query the database for the collection of groups the
user belongs to.
I wrote a macro as below. I'll copy and past my other post:

I have an access mdb file with an Admin user and a password set for the
user. So basically when I try to open the mdb file in Windows Explorer a
userform pops up
asking to provide user name and password for the database. If I don't
give a correct password or user name I cannot access the file. I tried
to use this in Excel VBA (see code below). It takes a user name and
password from a userform, connects to the database and retrieves each
table name. Unfortunately, if I provide a password (ie the same password
when opening the file through Windows Explorer) I cannot access the
file. I get an error: "Cannot start your application. The workgroup
information file is missing or opened exclusively by another user."

But if I leave the password blank it connects to the database and
retrieves table names. Why is that?

Private Sub cmdLogIn_Click()
Dim objCatalog As ADOX.Catalog
Dim objTable As ADOX.Table

Set objCatalog = New ADOX.Catalog

' Check if the database file path is set. If Not Len(gsDatabaseFilePath)

0 Then MsgBox gsMSG_DATABASE_FILE_NOT_SPECIFIED, vbInformation +
vbOKOnly, gsAPP_NAME

Exit Sub
End If

On Error GoTo ErrorHandler ' Check if user name is not a null string. If
Len(txtUserName.Text) > 1 Then gsDatabaseConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0; " & _ "Data Source=" &
gsDatabaseFilePath & "; " & _ "User ID=" & txtUserName.Text & "; " & _
"Password=" & txtPassword.Text objCatalog.ActiveConnection =
gsDatabaseConnectionString

For Each objTable In objCatalog.Tables MsgBox objTable.Name
Next objTable
Exit Sub
Else MsgBox gsMSG_WRONG_CREDENTIALS, vbInformation + vbOKOnly,
gsAPP_NAME Exit Sub
End If

ErrorHandler: MsgBox gsMSG_WRONG_CREDENTIALS & " " & Err.Description,
vbInformation + vbOKOnly, gsAPP_NAME

End Sub