如何强制 Access 更改无 DSN ODBC 连接的用户名和密码

Question

前言

我有一个带有MySQL后端的Access 2010前端。我想使用默认的MySQL用户名最初连接到后端数据库并进行密码检查等，然后切换到用户特定的MySQL用户名。

我已经创建了代码来更改连接字符串并使用新用户名重新连接MySQL表，但Access烦人地记住原始用户名和密码并使用该用户名和密码。

问题

如何强制 MS Access 2010 忘记原始用户名和密码以连接到 ODBC 并在连接字符串中使用新用户名和密码？

复制

重现我的问题

• MySQL：
  • 创建一个名为"test"的新架构
  • 在新架构上创建 3 个表：
    • "表1">
    • "表2">
    • "表3">
  • 创建两个有权访问该架构的新用户：
    • 名称："SQLUser1"，传递："传递01">
    • 名称："SQLUser2"，传递："传递02">
• 访问端：
  • 创建新的 MS Access 2010 项目
  • 创建新的空窗体
    • 添加2个名为"Cmd_User1"和"Cmd_User2"的按钮
  • 添加以下示例代码
    • 您需要在 GenConString(( 函数中更正服务器名称(sServer = "MySQL"(。
  • 运行表单
  • 点击"Cmd_User1"按钮
  • 点击"Cmd_User2"按钮
• 现在检查MySQL日志，它将使用用户："SQLUser1"用于两个连接:(

示例代码：

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

Answer 1

我认为

您最好的方法是使用第二个用户名仅与MySQL数据库建立一次连接。 至于检查密码等时的原始连接，您不能使用第一个用户名保存传递查询并使用它运行吗？