Excel和DB2连接 [英] Excel and DB2 connectivity

问题描述

我必须检查Excel和DB2之间的连接是否已经使用CLI / ODBC驱动程序建立。

I have to check whether the connection between Excel and DB2 has been established using CLI/ODBC driver or not.

为此，我打算写一个批处理文件，我将会调用excel表，反过来会自动执行一个宏，它会从sysibm.sysdummy1表中带出一些虚拟数据。

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.

我需要代码可以建立与我的数据库的连接，并且如果连接已建立，并且如果连接未建立，则通过发出一些成功消息来检查连接是否已建立。 （可能有一些问题出现的解释）

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)

推荐答案

您可以在DB2服务器和Excel使用ADODB（ActiveX数据对象）。有关示例连接字符串，请参阅此链接。

You can make an ODBC (or OleDB) connection between a DB2 server and Excel using ADODB (ActiveX Data Objects). See this link for sample connection strings.

此链接将显示您使用ADODB连接到数据库的示例VBA代码：
如何使用ADO与Visual Basic或VBA中的Excel数据

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

编辑：这里有一些简单而肮脏的示例代码。使用正确的连接字符串替换.connectionstring =部分。

EDIT: Here's some quick and dirty sample code. Replace the ".connectionstring =" portion with the proper connection string for your setup.

Dim cn As ADODB.Connection
Dim rsT 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

rsT.Open strSQL, cn

rsT.MoveFirst
Do Until rs.EOF
    Debug.Print rs.Fields(0)
    rsT.MoveNext
Loop

rsT.Close
cn.Close

Set rsT = Nothing
Set cn = Nothing

这篇关于Excel和DB2连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！