asked on

mySQL user authentication from MS Access

First of all I will explain what I am trying to do. I am running a MS Access front end with MySql backend. I have a login form that authenticates the user from tblUsers table. I have done this so I have the control to only permit certain people see certain forms and so on. What I would like to do is use the username and password to authenticate the MySQL DB so I can have much tighter control on Viewing, editing, deleting, adding of records. Any ideas how I can perform this task? Thanks in advance

Guy Hengel [angelIII / a3]

your configuration is that the mysql tables are defined as linked tables to the mysql database, right?

well, what you need is on login to modify the connection information of those linked table, using the login specified by the user.

however, in access 2007, I don't know if the code I used for lower version of access will work there...

Guy Hengel [angelIII / a3]

see here:
http://stackoverflow.com/questions/564265/how-to-refresh-linked-tables-in-an-access-mdb-when-odbc-changes

ASKER

I am not using the MS Access authentication scheme will this still work? I am using an authentication system that I designed (See code below). thanks

Private Sub CmdLogin_Click()
On Error GoTo Err_CmdLogin_Click
 
        If DCount("*", "qry_Login") = 0 Then
        
    MsgBox "Invalid Username or Password", vbOKOnly, "Login Error"
    [txtUserName] = Null
    [txtPassword] = Null
    DoCmd.GoToControl "txtUsername"
 
        '****************************************
 
    ElseIf [txtUserName] = DLookup("UserID", "qry_Login") And [txtPassword] = DLookup("Password", "qry_Login") _
        And [txtPassExpire] > Date Then
 
        Dim RS As Recordset
         Set RS = CurrentDb.OpenRecordset("SELECT * FROM tblUserInfo")
          RS.AddNew
          RS("UserName") = [txtUserName].value
          RS("Password") = [txtPassword].value
          RS("RoleID") = [txtRoleID].value
          RS("UserEmail") = [txtUserEmail].value
          RS("User") = [txtUserID].value
          RS("Action") = "Login"
          RS("Mill") = [txtMill].value
          RS("ActualUserID") = [txtActUserName].value
          RS("ComputerName") = [txtComputerName].value
          RS.Update
    
        Set RS = CurrentDb.OpenRecordset("Select * From sysLog")
            RS.AddNew
            RS("UserID") = [txtUserName].value
            RS("Action") = "LogIn"
            RS("ActUserName") = [txtActUserName].value
            RS("ComputerID") = [txtComputerName].value
            RS.Update
        RS.Close
    
    DoCmd.Close acForm, "Login", acSaveNo
    DoCmd.OpenForm "Switchboard", acNormal
    
    
        '****************************************
    
    ElseIf [txtUserName] = DLookup("UserID", "qry_Login") And [txtPassword] = DLookup("Password", "qry_Login") _
        And [txtPassExpire] <= Date Then
    
    With Me!txtUserName
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.value)
    End With
    
    DoCmd.RunCommand acCmdCopy
    
    txtUserID = Null
    txtPassword = Null
    
    MsgBox "Password has expired, please reset your password", vbOKOnly, "Password Error"
    
    DoCmd.OpenForm "frm_PasswordReset", acNormal
    
        '****************************************
    
    ElseIf [txtUserName] <> DLookup("UserID", "qry_Login") Or [txtPassword] <> DLookup("Password", "qry_Login") _
        Then 'And [chkInActive] = False Then
        
    MsgBox "Invalid Username or Password", vbOKOnly, "Login Error"
    [txtUserName] = Null
    [txtPassword] = Null
    DoCmd.GoToControl "txtUsername"
    
        '****************************************
    
    End If
        
        
        
Exit_CmdLogin_Click:
    Exit Sub
    
Err_CmdLogin_Click:
    MsgBox Err.Description
    Resume Exit_CmdLogin_Click
    
End Sub

Open in new window

Guy Hengel [angelIII / a3]

yes, except that for the refresh of the database links, you must skip the tblUserInfo, if that is also mapped to the underlying database.

ASKER

tblUserInfo is a local TEMP table. tblUsers is the main table that holds all the users and their information. on Login the Userinfo is held in the TEMP table "tblUserInfo" then deleted before the application exits. Should I refresh the linked table "tblUsers" on startup of the application? If the application cannot access this table then the user will not be able to log in or refresh the other tables. Thoughts?

Guy Hengel [angelIII / a3]

> Should I refresh the linked table "tblUsers" on startup of the application?
no.

ASKER

Please excuse my ignorance, but in the samples in the link above, I cannot find anywhere that it picks up the username and password from the login form or table. Can you help me understand where it does this at? Thanks again.

Guy Hengel [angelIII / a3]

see the "usage":
RefreshODBCLinks "ODBC;DRIVER=SQL Server Native Client 10.0;" & _"
"SERVER=SQLSERVER;UID=Administrator;" & _
"Trusted_Connection=Yes;" & _
"APP=2007 Microsoft Office system;DATABASE=OrderSystem;"

in there, you see UID=Administrator;Trusted_Connection=Yes;

you will have to replace that by:
UserID=" & your_user_name & ";pwd=" & your_password & ";"

ASKER

Ok, Thanks. I have tried to implement the code into the Login function but keep getting a Compile Error.

"Sub or Function not defined"

attached is the code that I am using.

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
 
        If DCount("*", "qry_Login") = 0 Then
        
    MsgBox "Invalid Username or Password", vbOKOnly, "Login Error"
    [txtUserName] = Null
    [txtPassword] = Null
    DoCmd.GoToControl "txtUsername"
 
        '****************************************
 
    ElseIf [txtUserName] = DLookup("UserID", "qry_Login") And [txtPassword] = DLookup("Password", "qry_Login") _
        And [txtPassExpire] > Date Then
 
        Dim UserName As String
        Dim Password As String
        UserName = [txtUserName]
        Password = [txtPassword]
        
 
       RefreshODBCLinks "ODBC;DRIVER=MySQL ODBC 3.51;" & "SERVER=YNGSTOWNSHARE;UserID=" & UserName & ",pwd=" & _
         Password & ";" & "Trusted_Conenction=Yes;" & "APP=2007 Microsoft Office system;DATABASE=LOGMGT;"
            
 
                 
        Dim RS As Recordset
         Set RS = CurrentDb.OpenRecordset("SELECT * FROM tblUserInfo")
          RS.AddNew
          RS("UserName") = [txtUserName].value
          RS("Password") = [txtPassword].value
          RS("RoleID") = [txtRoleID].value
          RS("UserEmail") = [txtUserEmail].value
          RS("User") = [txtUserID].value
          RS("Action") = "Login"
          RS("Mill") = [txtMill].value
          RS("ActualUserID") = [txtActUserName].value
          RS("ComputerName") = [txtComputerName].value
          RS.Update
    
        Set RS = CurrentDb.OpenRecordset("Select * From sysLog")
            RS.AddNew
            RS("UserID") = [txtUserName].value
            RS("Action") = "LogIn"
            RS("ActUserName") = [txtActUserName].value
            RS("ComputerID") = [txtComputerName].value
            RS.Update
        RS.Close
        
        
    
    DoCmd.Close acForm, "Login", acSaveNo
    DoCmd.OpenForm "Switchboard", acNormal
    
    
        '****************************************
    
    ElseIf [txtUserName] = DLookup("UserID", "qry_Login") And [txtPassword] = DLookup("Password", "qry_Login") _
        And [txtPassExpire] <= Date Then
    
    With Me!txtUserName
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.value)
    End With
    
    DoCmd.RunCommand acCmdCopy
    
    txtUserID = Null
    txtPassword = Null
    
    MsgBox "Password has expired, please reset your password", vbOKOnly, "Password Error"
    
    DoCmd.OpenForm "frm_PasswordReset", acNormal
    
        '****************************************
    
    ElseIf [txtUserName] <> DLookup("UserID", "qry_Login") Or [txtPassword] <> DLookup("Password", "qry_Login") _
        Then 'And [chkInActive] = False Then
        
    MsgBox "Invalid Username or Password", vbOKOnly, "Login Error"
    [txtUserName] = Null
    [txtPassword] = Null
    DoCmd.GoToControl "txtUsername"
    
        '****************************************
    
    End If
        
        
        
Exit_Command29_Click:
    Exit Sub
    
Err_Command29_Click:
    MsgBox Err.Description
    Resume Exit_Command29_Click
    
End Sub

Open in new window

ASKER

no other thoughts on this issue? Thanks in advance.

Guy Hengel [angelIII / a3]

>"Sub or Function not defined"

on which line?

ASKER

It didn't give me a line number, but attached is a screenshot of where the error occured.
vb-error.gif

Guy Hengel [angelIII / a3]

>RefreshODBCLinks
where is that function declared, actually?

ASKER

Thats a good question. where do I declare it at?

Guy Hengel [angelIII / a3]

the code for the function is in the links above...
but you have to modify it for your needs, ie to skip the table tblUsers in the loop.

where to place that code?
simply in a public module in you access database.

ASKER

I cannot get this thing figured out. I have tried everything in the links above, I'm just not getting the code right for some reason.

Guy Hengel [angelIII / a3]

It will be difficult to get that done remotely...
you might consider getting an experience access developer on-site...

ASKER

I am just not getting where to put the code to declare it. I haven't read anywhere a good explanation for this. I know its not that difficult to do, just understanding it I guess.

SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

ASKER

Ok. Here is the code I put into the new module named "RefreshODBCLinks"

Public Sub RefreshODBCLinks(newConnectionString As String)

Dim db As DAO.Database
Dim tb As DAO.TableDef

Set db = CurrentDb

For Each tb In db.TableDefs

If Left(tb.Connect, 4) = "ODBC" Then
tb.Connect = newConnectionString
tb.RefreshLink
Debug.Print "Refreshed ODBC table " & tb.Name
End If

Next tb

Set db = Nothing

End Sub

Below is the code that I have in the LogIn button event. Now I am getting the error attached in the image. Thanks for all your help AND PATIENCE!!

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
 
 
        Dim UserName As String
        Dim Password As String
        UserName = [txtUserName]
        Password = [txtPassword]
        
 
       RefreshODBCLinks "ODBC;DRIVER=MySQL ODBC 3.51;" & "SERVER=localhost;UserID=" & UserName & ",pwd=" & _
         Password & ";" & "Trusted_Conenction=Yes;" & "APP=2007 Microsoft Office system;DATABASE=LogisticsMgmt_TEST;"
 
        
        
        If DCount("*", "qry_Login") = 0 Then
        
    MsgBox "Invalid Username or Password", vbOKOnly, "Login Error"
    [txtUserName] = Null
    [txtPassword] = Null
    DoCmd.GoToControl "txtUsername"
 
        '****************************************
 
    ElseIf [txtUserName] = DLookup("UserID", "qry_Login") And [txtPassword] = DLookup("Password", "qry_Login") _
        And [txtPassExpire] > Date Then
 
                 
        Dim RS As Recordset
         Set RS = CurrentDb.OpenRecordset("SELECT * FROM tblUserInfo")
          RS.AddNew
          RS("UserName") = [txtUserName].value
          RS("Password") = [txtPassword].value
          RS("RoleID") = [txtRoleID].value
          RS("UserEmail") = [txtUserEmail].value
          RS("User") = [txtUserID].value
          RS("Action") = "Login"
          RS("Mill") = [txtMill].value
          RS("ActualUserID") = [txtActUserName].value
          RS("ComputerName") = [txtComputerName].value
          RS.Update
    
        Set RS = CurrentDb.OpenRecordset("Select * From sysLog")
            RS.AddNew
            RS("UserID") = [txtUserName].value
            RS("Action") = "LogIn"
            RS("ActUserName") = [txtActUserName].value
            RS("ComputerID") = [txtComputerName].value
            RS.Update
        RS.Close
        
        
    
    DoCmd.Close acForm, "Login", acSaveNo
    DoCmd.OpenForm "Switchboard", acNormal
    
    
        '****************************************
    
    ElseIf [txtUserName] = DLookup("UserID", "qry_Login") And [txtPassword] = DLookup("Password", "qry_Login") _
        And [txtPassExpire] <= Date Then
    
    With Me!txtUserName
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.value)
    End With
    
    DoCmd.RunCommand acCmdCopy
    
    txtUserID = Null
    txtPassword = Null
    
    MsgBox "Password has expired, please reset your password", vbOKOnly, "Password Error"
    
    DoCmd.OpenForm "frm_PasswordReset", acNormal
    
        '****************************************
    
    ElseIf [txtUserName] <> DLookup("UserID", "qry_Login") Or [txtPassword] <> DLookup("Password", "qry_Login") _
        Then 'And [chkInActive] = False Then
        
    MsgBox "Invalid Username or Password", vbOKOnly, "Login Error"
    [txtUserName] = Null
    [txtPassword] = Null
    DoCmd.GoToControl "txtUsername"
    
        '****************************************
    
    End If
        
        
        
Exit_Command29_Click:
    Exit Sub
    
Err_Command29_Click:
    MsgBox Err.Description
    Resume Exit_Command29_Click
    
End Sub

Open in new window

compile-error.gif

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

ASKER

we are getting closer. Its trying to connect now. Here is the code

RefreshODBCLinks "ODBC;DRIVER=MySQL ODBC 3.51;" & _
"SERVER=localhost;UserID=" & username & ";pwd=" & password & ";" & _
"Trusted_Connection=Yes;" & _
"APP=2007 Microsoft Office system;DATABASE=LogisticsMgmt_TEST_2009-03-231;"

I'm getting the failed to connect error now (see attached). I am using the following parameters

MySql DB name: logmgmt_local
Server: localhost
access DB name: LogisticsMgmt_TEST_2009-03-231

login-error.gif

SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

ASKER

same error.

Guy Hengel [angelIII / a3]

can you connect to the mysql with the mysql tools directly?
you might consider to create a DSN entry, which you can test connectivity, and eventually use that instead for the connection...
anyhow, solving that should be a dedicated question (in the MySQL zone).

ASKER

Yes, I am connected using mysql tools, EMS SQL Manager, and linked to the tables in Access. The problem is that I want to refresh the tables on startup of the application to ensure no problems exist. This is where I am stumped. Thanks for all your help, I'm sure WE will get this thing defeated!!

ASKER

OK HERE IT IS!

Dim Username As String
Dim Password As String
Username = [txtUserName]
Password = [txtPassword]

RefreshODBCLinks "ODBC;Driver={MySQL ODBC 3.51 Driver};" & _
"Server=localhost;Database=Logmgmt_local; User=" & Username & ";Password=" & Password & ";" & _
"Trusted_Connection=Yes;" & _
"APP=2007 Microsoft Office system;DATABASE=[LogisticsMgmt_TEST];"

It took the brackets around the Driver to work.

ASKER

Thanks for all your help on this one and especially the patience of my inabilities. lol