ms-access中与mysql服务器的无DSN连接不记住用户名和密码 [英] DSN-less connection to mysql server in ms-access not remembering user name and password

问题描述

为了便于分发，我希望所有mysql链接表都不含DSN.我还希望能够轻松地将表重新链接到其他服务器(出于测试目的)，因此我正在用vba代码进行链接设置.这是我用来设置/刷新任何链接的代码:

For ease of distribution I want all the mysql linked tables to be DSN-less. I also want to be able to relink the tables to a different server easily (for test purposes), so I'm doing the link setup in vba code. This is the code I use to setup/refresh any links:

Private Sub relink_mysql_tables(mysql_connection As String)
    Dim db As Database
    Set db = CurrentDb()
    Dim tbldef As TableDef
    For Each tbldef In db.TableDefs
        If (tbldef.Attributes And TableDefAttributeEnum.dbAttachedODBC) Then
            tbldef.Connect = mysql_connection
            tbldef.RefreshLink
        End If
    Next
End Sub

我使用的mysql_connection字符串是:

The mysql_connection string I use is:

DRIVER={MySQL ODBC 5.1 Driver};Server=myserver;Database=mydatabase;Uid=myusername;Pwd=mypassword;Option=3

一切正常，一切正常，直到我关闭ms-access并重新打开它.用户名和密码似乎被忘记了.例如，如果尝试打开链接表，则会看到ODBC连接器弹出窗口，要求我输入用户名和密码.

This is all fine and dandy and everything works until I close ms-access and reopen it. The user name and password seems to get forgotten. If I try and open a linked table, for example, I'm presented with a ODBC Connector pop-up asking me to enter user name and password.

我注意到，在运行上面的重新链接代码后，如果将鼠标指针悬停在链接的表(在本例中为投标"表)上，它将显示当前的连接字符串，但省略了用户名和密码:

I notice that after running the above relink code that if I hover the mouse pointer over a linked table (a table called 'tender' in this instance) it shows the current connection string, but with the user name and password omitted:

ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=myserver;Database=mydatabase;Option=3;TABLE=tender

我在其他地方读到，在连接字符串中添加; Persist Security Info = True"可以，但是不行！下次我重新启动ms-access时，仍然忘记了用户名和密码.我当前的解决方案是每次启动数据库时都重新运行重新链接代码，但这对我来说似乎是不必要的开销.是一种访问方式来记住链接表中的用户名和密码的方法吗?

I read elsewhere that adding ";Persist Security Info=True" to the connection string would work but it doesn't! The user name and password is still forgotten the next time I restart ms-access. My current solution is to re-run the relink code every-time the database is started but this seems like an unnecessary overhead to me. Is the a way for access to remember the user name and password within the linked tables?

我正在使用Mysql5.5和Access2007.

I'm using Mysql5.5 and Access2007 by the way.

推荐答案

我在此微软文章.而不是使用RefreshLink方法，而是使用dbAttachedODBC选项删除并重新创建链接:

I found an answer with the help of this microsoft article. Rather than using the RefreshLink method, delete and recreate the link with the dbAttachedODBC option:

Public Sub relink_mysql_tables(mysql_connection As String)

Dim db As Database
Dim tblDef As TableDef
Dim sLocalTableName As String
Dim sRemoteTableName As String    

' new collection '
Dim newTableDefs As New Collection

' current database '
Set db = CurrentDb()

' create new table defs '
For Each tblDef In db.TableDefs
    If (tblDef.Attributes And TableDefAttributeEnum.dbAttachedODBC) Then
        sLocalTableName = tblDef.Name
        sRemoteTableName = tblDef.SourceTableName

        ' create new linked table def '
        Set tblDef = db.CreateTableDef(sLocalTableName, dbAttachSavePWD, sRemoteTableName, mysql_connection)
        newTableDefs.Add tblDef         

    End If
Next

' delete old table defs '
For Each tblDef In newTableDefs
    db.TableDefs.Delete tblDef.Name
Next

' add new table defs to current database '
For Each tblDef In newTableDefs
    db.TableDefs.Append tblDef
Next

连接字符串与以前相同，但增加了前缀"ODBC;":

The connection string is the same as before but with the addition of the prefix "ODBC;":

ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=myserver;Database=mydatabase;Uid=myusername;Pwd=mypassword;Option=3

这篇关于ms-access中与mysql服务器的无DSN连接不记住用户名和密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！