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)

推荐答案

您可以使用 ADODB(ActiveX 数据对象)在 DB2 服务器和 Excel 之间建立 ODBC(或 OleDB)连接.有关示例连接字符串，请参阅此链接.

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 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

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