连接&使用Excel VBA从IBM DB2获取数据 [英] Connect & Fetch Data from IBM DB2 with Excel VBA

问题描述

我想从excel VBA与IBM DB2建立连接，并在excel表中获取结果。我在Sheet1中有一个按钮，在单击按钮时，结果应该从DB2获取到excel（Sheet2，从Cell A1开始）

I would like to establish a connection with IBM DB2 from excel VBA and fetch the result in excel sheet. I have a button in Sheet1 and on clicking on the button the result should be fetched from DB2 to excel (Sheet2, starting from Cell A1)

我不太熟悉代码它与DB2建立连接。所以，我提出了下面的代码片段，但在运行时收到错误消息 - 未找到数据源名称且未指定默认驱动程序。

I’m not much familiar with the code when it comes to making a connection with DB2. So, I came up with the below snippet but getting an error message while running it – Data source name not found and no default driver specified.

Dim conn As Object'ADODB.Connection的变量object

Dim conn As Object 'Variable for ADODB.Connection object

Dim rs As Object'变量用于ADODB.Recordset对象

Dim rs As Object 'Variable for ADODB.Recordset object

设置conn = CreateObject（" ADODB.Connection"）

Set conn = CreateObject("ADODB.Connection")

设置rs = CreateObject（" ADODB.Recordset"）

Set rs = CreateObject("ADODB.Recordset")

conn.Open" DB_Name"，" Username"，" Password"

conn.Open "DB_Name", "Username", "Password"

rs.Open" Select * from DB_Name.EMP_Table;"，conn

rs.Open "Select * from DB_Name.EMP_Table;", conn

rs.Close

conn .Close

conn.Close

我在IBM Data Studio中配置的其他细节很少。我是否需要在代码中提及这些细节？

Few additional details I have configured in IBM Data Studio. Do I need to mention these details in the code?

驱动程序 - 用于JDBC和SQLJ的IBM数据服务器驱动程序（JDBC 4.0）默认

Driver – IBM Data Server Driver for JDBC and SQLJ (JDBC 4.0) Default

数据库管理器 - DB2 for linux，Unix和windows

Database Manager – DB2 for linux, Unix and windows

提前致谢！

JPP

推荐答案

您好
John P Parker，

我尝试引用代码并发现它在IBM支持页面上可用。

I try to refer the code and find that it is available on IBM Support page.

使用ADO连接到IBM DB2，Microsoft SQL Server或Oracle数据库

在该页面中，提到您需要安装' IBM DB2 ODBC DRIVER'。

In that page, It is mentioned that you need to install 'IBM DB2 ODBC DRIVER'.

因此，如果你之前没有安装此驱动程序，你可以尝试安装它并尝试用它进行测试。

So if you did not install this driver before then you can try to install it and try to make a test with it.

在其他线程中，我找到了ODBC的例子连接器。

In other Thread, I find example with ODBC Connectors.

Private Sub query()
  DBCONSRT = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=NZ1;DSN=NZ1;UID=user;SDSN=;HST=ibslnpb1.sysplex.homedepot.com;PRT=4101;In‌​itial Catalog=PRTHD;"
  Using connection = New OleDbConnection(DBCONSRT )
      connection.Open()
      Dim cmd = connection.CreateCommand()
      cmd.CommandText = QRYSTR //This is where your sql statement should go, or the variable that is equal to the query.
      Using dr = cmd.ExecuteReader()
          //Process your query results here 
      End Using
  End Using
End Sub 

参考：

来自excel的DB2连接宏

免责声明：此回复包含对第三方万维网站点的引用。 Microsoft提供此信息是为了方便您。 Microsoft不控制这些网站，也未测试在这些网站上找到的任何软件或信息;因此，Microsoft不能就其中发现的任何软件或信息的质量，安全性或适用性做出任何陈述。使用互联网上的任何软件都存在固有的危险，并且Microsoft提醒您在从互联网上检索任何软件之前确保您完全了解风险的
。

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

问候

Deepak

这篇关于连接&使用Excel VBA从IBM DB2获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！