Access VBA生成的ODBC连接字符串将还原

Question

我正在为位于本地共享服务器上的SQL Server(Express 2019(数据库创建本地MS Access(365(前端应用程序。

我有一个登录表单，当用户登录时，它会重新链接所有链接的表和视图

基本上，我在Access应用程序中有一个本地表，它列出了登录时需要重新链接的所有表名。登录时，当前链接被删除，然后代码在表列表中循环，并根据过程中构建的连接字符串(包括UID和PWD(链接它们。但当我在登录后检查连接字符串时，它不包括登录信息。只有在"链接表管理器"中手动编辑字符串后，才能连接到其中一个链接表的Excel工作簿。

以下是登录过程的代码：

Private Sub cmdConnect_Click()
Dim db As Database
Dim tdf As TableDef
Dim rst As Recordset
Dim rst1 As Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strConnect As String
Dim strMsg As String
Dim strPass As String
Dim strPrimary As String
On Error GoTo HandleErr
Set db = CurrentDb
strPass = DLookup("[Password]", "tblUsers", "[User] = '" & Me.txtUser & "'")
If StrComp(Me.txtPwd, strPass, vbBinaryCompare) <> 0 Then
strMsg = "Incorrect Username or password!"
GoTo ExitHere
End If

' Create a recordset to obtain server object names.

Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If
'Assign the current user in table
Set rst1 = db.OpenRecordset("tblUsers", dbOpenDynaset, dbSeeChanges)
With rst1
.MoveFirst
Do Until rst1.EOF
.Edit
Select Case !user
Case Me.txtUser
!Current = -1

Case Else
!Current = 0

End Select
.Update
.MoveNext
Loop
End With
strConnect = "ODBC;Driver={ODBC Driver 17 for SQL Server};Trusted_Connection=No;DSN=SQL1;UID=" _
& Me.txtUser & ";PWD=" & Me.txtPwd & ";"
'delete all existing linked tables
Call deleteLinks

' Walk through the recordset and create the links.
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object.
Set tdf = db.CreateTableDef("dbo_" & strTable, 0, "dbo." & strTable, strConnect & "Server=" & strServer & ";Database=" & strDB & ";")

' Set the Connect property to establish the link.

db.TableDefs.Append tdf
Debug.Print tdf.Connect
Set tdf = Nothing

rst.MoveNext
Loop
strMsg = "Tables linked successfully."

rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
DoCmd.Close acForm, Me.name
DoCmd.OpenForm "frmStart"
ExitHere:
MsgBox strMsg, , "Link SQL Tables"
Exit Sub
HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select

End Sub
Private Sub deleteLinks()
Dim rst As Recordset
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.name Like "dbo_*" Then
DoCmd.DeleteObject acTable, tdf.name
End If
Next
End Sub

当我查看即时窗口以查看打印的tdf.connect时，它会给我：

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=RNC1SQLSQLEXPRESS;UID=****;PWD=*************;Trusted_Connection=No;APP=Microsoft Office;DATABASE=RNCMasterfile;

但是，当我在链接表管理器中查看连接字符串时，我会得到以下内容：

DRIVER=ODBC Driver 17 for SQL Server;SERVER=RNC1SQLSQLEXPRESS;Trusted_Connection=No;APP=Microsoft Office;DATABASE=RNCMasterfile;

奇怪的是，我可以访问和使用access中的表，但我有Excel电子表格，它基于链接的表连接到access查询，如果连接字符串不包含登录信息，它们就不起作用。

有什么想法可以通过编程方式强制连接字符串包含此信息吗？

Answer 1

尝试使用DoCmd.TransferDatabase链接表。

DoCmd.TransferDatabase acLink, "ODBC Database", [your_cnn_string], acTable, [source_tbl_name], [linked_table_name], , True

TranserDatabase中的最后一个选项是StoreLogin。

我在自己的应用程序中使用此功能。

点击此处了解TransferDatabase。