mySQL user authentication from MS Access

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...

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

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

Select allOpen in new window

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

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?

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

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.

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 & ";"

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

Select allOpen in new window

no other thoughts on this issue?  Thanks in advance.

>"Sub or Function not defined"

on which line?

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

>RefreshODBCLinks
where is that function declared, actually?

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

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.

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.

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

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.  

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

Select allOpen in new window

compile-error.gif

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

same error.

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).

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!!

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.

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