尝试动态分配SQL Server名称和数据库

Question

不知道我做错了什么。当它传递服务器名称时，它传递的是txtlink3而不是服务器名称。查看屏幕截图了解更多细节

运行代码后的服务器连接。

服务器名和数据库名来自的表单

感谢你的帮助。

Private Sub cmdlink1_Click()
On Error GoTo Err_cmdlink1_Click
DoCmd.SetWarnings False
Dim strServer As String
Dim strDatabase As String
strServer = txtlink3   'This comes from a Field in the form
strDatabase = txtlink4 'This comes from a Field in the form 

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC; Driver={SQL Server};Server=txtlink3;Database=txtlink4;Trusted_Connection=Yes", acTable, "dbo.address", "AddressMS" ' Use for test SQLdb on matts workstation
MsgBox "Done"
DoCmd.SetWarnings True
Exit_cmdlink1_Click:
Exit Sub
Err_cmdlink1_Click:
MsgBox Err.Description
Resume Exit_cmdlink1_Click

我已经为访问做了类似的事情，它工作得很好，见下面的代码

Private Sub cmdlink2_Click()
On Error GoTo Err_cmdlink2_Click
DoCmd.SetWarnings False
If DLookup("[license #]", "Ticket") <> DLookup("strlicense", "tblsmsettings") Or IsNull(DLookup("strlicense", "tblsmsettings")) = True Then
MsgBox "You don't have a License to use this product. Please contact the software vendor", vbOKOnly
Exit Sub 'be sure that they have a license to use this app
End If
Dim strFile As String
strFile = txtlink2
If Dir(strFile) = "" Then ' see if the file exists before dropping
MsgBox "The database specified does not exist"
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "drop Table [Journal Disbursements]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Disbursements", "Journal Disbursements"
DoCmd.RunSQL "drop table [Journal Receipts]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Receipts", "Journal Receipts"
DoCmd.RunSQL "drop table [Journal General]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal General", "Journal General"
DoCmd.RunSQL "drop table [Journal Purchases]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Purchases", "Journal Purchases"
DoCmd.RunSQL "drop table [Journal Sales]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Sales", "Journal Sales"
DoCmd.RunSQL "drop table [Ticket]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Ticket", "Ticket"
MsgBox "Done"
DoCmd.SetWarnings True
End If
Exit_cmdlink2_Click:
Exit Sub
Err_cmdlink2_Click:
MsgBox Err.Description
Resume Exit_cmdlink2_Click

End Sub

Answer 1

如果你想用动态元素构建连接字符串，那么必须连接变量输入。

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC; Driver={SQL Server};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=Yes", _
acTable, "dbo.address", "AddressMS"