用于连接 ODBC 以在 Excel 中检索数据的 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24555892/
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
VBA macro for connexion with ODBC for retrieve data in excel
提问by xif
I try to retrieve some SQL data with excel. It works fine with a usual query (data->other sources->microsoft query.)
我尝试用 excel 检索一些 SQL 数据。它适用于通常的查询（数据->其他来源->微软查询。）
I try to create a maccro for that, and i'm actually block with the connexion/identification.
我尝试为此创建一个宏，但实际上我被连接/识别阻止了。
a piece of my macro below :
下面是我的一段宏：
Sub Test()
'Declare a Connection object
Dim cnDB As New ADODB.Connection
'Declare a Recordset Object
Dim rsRecords As New ADODB.Recordset
cnDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;DSN=ServiceNow;User ID=test;Password=testtest;"
The debug say the cnDB.Open is not good : "execution erro '-2147418113 (8000ffff)' Catastrophic failure". I've already tried many way to fix that but I didn't find.. Thanks for your help.
调试说 cnDB.Open 不好：“执行错误 '-2147418113 (8000ffff)' 灾难性故障”。我已经尝试了很多方法来解决这个问题，但我没有找到..谢谢你的帮助。
回答by user2063329
I created ODBC DSN for SQL Server 2012 Express Then I recorded a macro in MS EXCEL 2010 (Use Developer tab). There was no problem.
我为 SQL Server 2012 Express 创建了 ODBC DSN 然后我在 MS EXCEL 2010（使用开发人员选项卡）中录制了一个宏。没有问题。
My connection information is as shown here:
我的连接信息如下所示：
"ODBC;DSN=sqlSrvr2012;UID=mysorian;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=HODENTEKWIN7;DATABASE=Northwind;"
"ODBC;DSN=sqlSrvr2012;UID=mysorian;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=HODENTEKWIN7;DATABASE=Northwind;"
Looks like you are getting data from MS ACCESS. It should be no different, perhaps you MS Access database needs correct authentication information. The complete code is in my G+
看起来您正在从 MS ACCESS 获取数据。应该没有什么不同，也许您的 MS Access 数据库需要正确的身份验证信息。完整代码在我的 G+
回答by IAmDranged
What is the database you are looking to retrieve data from?
您要从中检索数据的数据库是什么？
This may help - http://www.connectionstrings.com
这可能会有所帮助 - http://www.connectionstrings.com
