vba Excel 和 DB2 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7171546/
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
Excel and DB2 connectivity
提问by Himanshu Mishra
I have to check whether the connection between Excel and DB2 has been established using CLI/ODBC driver or not.
我必须检查是否使用 CLI/ODBC 驱动程序建立了 Excel 和 DB2 之间的连接。
For that I plan to write a batch file where I will be calling the excel sheet which in turn will automatically execute a macro which will bring out some dummy data from the sysibm.sysdummy1 table.
为此，我计划编写一个批处理文件，我将在其中调用 Excel 工作表，该工作表将自动执行一个宏，该宏将从 sysibm.sysdummy1 表中取出一些虚拟数据。
I require code with which I can make a connection to my database and check if the connection has been established or not by giving out some success message if the connection was established and a failure message if the connection was not established. (Probably with some explanation where the problem occurred)
我需要代码来连接到我的数据库并检查连接是否已建立，如果连接已建立，则发出一些成功消息，如果连接未建立，则发出失败消息。（可能对问题发生的地方有一些解释）
回答by Banjoe
You can make an ODBC (or OleDB) connection between a DB2 server and Excel using ADODB (ActiveX Data Objects). See this linkfor sample connection strings.
您可以使用 ADODB（ActiveX 数据对象）在 DB2 服务器和 Excel 之间建立 ODBC（或 OleDB）连接。有关示例连接字符串，请参阅此链接。
This link will show you sample VBA code to use with ADODB to connect to your database: How To Use ADO with Excel Data from Visual Basic or VBA
此链接将向您展示与 ADODB 一起使用以连接到您的数据库的示例 VBA 代码： 如何将 ADO 与来自 Visual Basic 或 VBA 的 Excel 数据一起使用
EDIT: Here's some quick and dirty sample code. Replace the .connectionstring =portion with the proper connection string for your setup.
编辑：这是一些快速而肮脏的示例代码。用.connectionstring =适合您的设置的正确连接字符串替换该部分。
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM sysibm.sysdummy1 FETCH FIRST 10 ROWS ONLY"
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path & _
"\ExcelSrc.xls;Extended Properties=Excel 8.0;"
.Open
End With
rs.Open strSQL, cn
rs.MoveFirst
Do Until rs.EOF
Debug.Print rs.Fields(0)
rs.MoveNext
Loop
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
