在 VBA 中设置 ODBC 连接字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28219298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting an ODBC connection string in VBA
提问by CactusCake
I have created a macro that sends a new CommandText to an ODBC Connection in my Excel spreadsheet and then refreshes the table of results associated with the query. This has been working fine, but I've noticed that each time I run the macro it overwrites the connection string with some default values that work on my machine but will not work on other users' machines because they do not have the saved connection file that I have. The more specific connection string that specifies a server address works when entered manually, but will get overwritten anytime the macro is run.
我创建了一个宏，该宏将新的 CommandText 发送到 Excel 电子表格中的 ODBC 连接，然后刷新与查询关联的结果表。这一直工作正常，但我注意到每次运行宏时，它都会用一些默认值覆盖连接字符串，这些默认值在我的机器上工作但在其他用户的机器上不起作用，因为他们没有保存的连接文件我有。指定服务器地址的更具体的连接字符串在手动输入时有效，但在运行宏时会被覆盖。
I figured I would just have the macro write the connection string at the same time it sends the new CommandText, but I'm running into errors.
我想我只会让宏在发送新的 CommandText 的同时写入连接字符串，但我遇到了错误。
My code is as follows:
我的代码如下：
Sub NewData()
Dim lStr As String
lStr = ""
lStr = lStr & " USE myDBname; "
lStr = lStr & " WITH X AS ("
lStr = lStr & " SELECT"
lStr = lStr & " column1, column2, column3, etc"
lStr = lStr & " FROM"
lStr = lStr & " etc. etc. etc."
With ActiveWorkbook.Connections("PayoffQuery").ODBCConnection
.CommandText = lStr
.Connection = "SERVER=myserveraddress;UID=SYSTEM;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=SYSTEM;DATABASE=myDBname;"
End With
End Sub
The .CommandText still updates just fine, but the .Connection throws runtime error 1004: Application-defined or object-defined error.
.CommandText 仍然更新得很好，但 .Connection 会引发运行时错误 1004：应用程序定义或对象定义错误。
Any idea what I'm doing wrong here? TIA.
知道我在这里做错了什么吗？TIA。
采纳答案by ExactaBox
In your VBA code, add ODBC;to the beginningof your new connection string.
在 VBA 代码中，添加ODBC;到新连接字符串的开头。
.Connection = "ODBC;SERVER=myserveraddress;UID=SYSTEM;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=SYSTEM;DATABASE=myDBname;"
