1

Preface

I have a Access 2010 front-end with a MySQL back-end. I want to use a default MySQL user-name to initially connect to the back-end DB and do password checks etc then switch to a user specific MySQL user-name.

I have already created code to change the connection string and reconnect the MySQL tables with the new user-name but Access annoyingly keep remembering the original user-name and password and uses that one.

Question

How can I force MS Access 2010 to forget the original user-name and password to connect to an ODBC and use the new one in the connection string?

Replication

To recreate my problem

  • MySQL:
    • Create a new schema called "test"
    • Create 3 tables on the new schema:
      • "table1"
      • "table2"
      • "table3"
    • Create two new users that have access to that schema:
      • Name: "SQLUser1", Pass: "Pass01"
      • Name: "SQLUser2", Pass: "Pass02"
  • Access End:
    • Create a new MS Access 2010 project
    • Create a new empty form
      • Add 2 buttons called "Cmd_User1" and "Cmd_User2"
    • Add the example code below
      • You will need to correct the server name (sServer = "MySQL") in GenConString() function.
    • Run the form
    • Click "Cmd_User1" button
    • Click "Cmd_User2" button
  • Now check the MySQL logs and it will have used user: "SQLUser1" for both connections :(

Example Code:

Option Compare Database
Option Explicit

Private Sub Cmd_User1_Click()
    'Remove all existing tables
    Call RemoveAllTables
    'Connect the tables
    Call AttachDSNLessTable("table1", "table2", GenConString("SQLUser1", "Pass01"))
    Call AttachDSNLessTable("table2", "table2", GenConString("SQLUser1", "Pass01"))
    Call AttachDSNLessTable("table3", "table3", GenConString("SQLUser1", "Pass01"))
End Sub

Private Sub Cmd_User2_Click()
    'Remove all existing tables
    Call RemoveAllTables
    'Connect the tables
    Call AttachDSNLessTable("table1", "table1", GenConString("SQLUser2", "Pass02"))
    Call AttachDSNLessTable("table2", "table2", GenConString("SQLUser2", "Pass02"))
    Call AttachDSNLessTable("table3", "table3", GenConString("SQLUser2", "Pass02"))
End Sub

Private Sub RemoveAllTables()
    Dim bFound As Boolean, TblDef As DAO.TableDef
    bFound = True 'Force to loop once
    While (bFound = True)
        bFound = False
        For Each TblDef In CurrentDb.TableDefs
            If Not (TblDef.Connect = "") Then
                Call CurrentDb.TableDefs.Delete(TblDef.Name)
                bFound = True
            End If
        Next TblDef
    Wend
End Sub

Private Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stConnect As String)
    On Error GoTo AttachDSNLessTable_Err

    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function

Private Function GenConString(ByVal sUserName As String, ByVal sPassword As String) As String
    Dim sConString As String, sServer As String, sDatabase As String, iPort As Integer
    'Pull back all the required fields
    sServer = "MySQL"
    sDatabase = "test"
    iPort = "3306"
    'Generate connection string
    sConString = "ODBC;Driver={MySQL ODBC 5.1 Driver}; " _
                & "Server=" & sServer & "; " _
                & "Database=" & sDatabase & "; " _
                & "UID=" & sUserName & "; " _
                & "PWD=" & sPassword & "; " _
                & "Port=" & iPort & "; " _
                & "Option=3"
    'Return new connection string
    GenConString = sConString
End Function
GazB
  • 3,510
  • 1
  • 36
  • 42
  • After hours of digging I suspect it could be "ODBC Connection Pooling" that is causing this issue. See: http://support.microsoft.com/kb/169470 BUT not YET found out how to check if it is being used and more importatly how to clear the pool so a new set are created... Any ideas? – GazB Dec 09 '11 at 07:53
  • Maybe not found this: http://support.microsoft.com/kb/216950/EN-US and it appears to be disabled for my MySQL drivers so can't see how it can be that. Back to drawing board... :( – GazB Dec 09 '11 at 08:01
  • Just a thought, may or may not help : http://www.tek-tips.com/viewthread.cfm?qid=556609 – Fionnuala Dec 09 '11 at 13:21
  • Hehe hi again Remou. From what I can see that is just telling you to ensure nothing connects to the backend db until you have the final username. As I need to do connections before that it isn't any good. Thanks though. – GazB Dec 09 '11 at 16:42

1 Answers1

1

I reckon your best approach is to only have one connection to your MySQL database, using your second username. As for the original connection when you are checking the passwords etc, could you not save a pass-through query with the first username and run with that?

Rahul Nikate
  • 6,192
  • 5
  • 42
  • 54
Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
  • I'm not sure I understand your suggestion Matt. It sounds like your suggesting I only use one user-name and password to connect and don't change them which seems to kinda defeat the object of the question as I currently have that method (the system connects to MySQL with a generic user-name and then the user logs in with their application specific user-name) and want to change the MySQL's User-name when the user logs in with their own application specific user-name/password but due to the clearing the Access cache issue this question is for I can't do that. :( – GazB Dec 13 '11 at 09:51
  • @Zasurus Apologies, I thought that it was your user1 which was checking the passwords, but it's actually a generic account not listed in the code example above. In this case then, can't you ignore linking your tables and use an ADO connection to the MySQL database instead. You would then be able to close the connection from one user and open up another with a different username? – Matt Donnan Dec 13 '11 at 10:35
  • If I ignore the re-linking of my tables then it would be using the MySQL user-name that they were linked with when access was last closed down? How would using an ADO connection make any difference? Wouldn't it still cache the MySQL user-name & password for the connection just as it does now or are you suggesting I connection with a method other than ODBC? If so what is that and how does it work? – GazB Dec 13 '11 at 15:04
  • @Zasurus Im suggesting not having linked tables at all (sorry I didn't make that clear) and then interact with your database using ado recordsets instead rather than the native 'DAO' which access uses with linked tables, 'DAO' is good when you are using the Jet database engine but for external data sources 'ADO' is better as it is tailored to be able to interact with most data sources. There is an example here: http://www.sqlstrings.com/MySQL-connection-strings.htm you still use the ODBC driver but any actions you take are reletive to the current connection you have open. – Matt Donnan Dec 13 '11 at 16:49
  • Thanks I will have to look into ADO in more depth to see if I can use it. It does sound like a good option and if it allows me to get around this issue with Access caching the username and refusing to change it without a reload of access then it may solve the problem. Will get back when I know more about ADO. :) – GazB Dec 14 '11 at 08:56
  • Actually, the best approach is to NOT include the user name and password in the linked tables. And you do NOT want (or need) to delete the linked tables and re-create them. Execute a logon, and all linked tables will NOW used that logged in user. If you execute another logon, then again linked tables should work. Suggestions here to re-build the application and forms using ADO is BEYOND stupid! Unfortanly, the cache cannot be flushed unless you exit the application, but this should still allow you to execute a logon for a given user/pass and NOT re-link. – Albert D. Kallal Dec 15 '15 at 21:17
  • How to logon without table re-linking is outlined here: http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx – Albert D. Kallal Dec 15 '15 at 21:18