如何强制Access更改少DSN的ODBC连接的用户名和密码? [英] How can I force Access to change the user-name and password for a DSN less ODBC connection?

问题描述

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

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.

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

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.

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

重现我的问题

• MySQL:
  • 创建一个称为测试"的新架构
  • 在新架构上创建3个表:
    • 表1"
    • 表2"
    • "table3"
    • MySQL:
      • Create a new schema called "test"
      • Create 3 tables on the new schema:
        • "table1"
        • "table2"
        • "table3"
        • 名称:"SQLUser1"，密码:"Pass01"
        • 名称:"SQLUser2"，密码:"Pass02"
        • 创建一个新的MS Access 2010项目
        • 创建一个新的空表格
          • 添加2个名为"Cmd_User1"和"Cmd_User2"的按钮
          • Create a new MS Access 2010 project
          • Create a new empty form
            • Add 2 buttons called "Cmd_User1" and "Cmd_User2"
            • 您将需要在GenConString()函数中更正服务器名称(sServer ="MySQL").

            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
            

            推荐答案

            我认为您最好的方法是使用第二个用户名仅与您的MySQL数据库建立一个连接.至于检查密码等时的原始连接，是否可以不使用第一个用户名保存传递查询并使用该用户名运行?

            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?

            这篇关于如何强制Access更改少DSN的ODBC连接的用户名和密码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！