来自excel宏的DB2连接 [英] DB2 connection from excel macro

问题描述

我想从excel宏连接到DB2 ...这是我的代码，但它不起作用，它给出错误为运行时错误...任何人都可以帮助我...

I want to connect to DB2 from excel macro...This is my code, but it not working, Its giving error as 'Run-time Error'...Can anyone help me...

Option Explicit

Dim DBCONSRT, QRYSTR As String

Dim DBCON, DBRS  As Object

Private Sub query()
    DBCONSRT = "Driver=jdbc:db2://my_host;Database=PRTHD;hostname=NZ1;port=5355;protocol=TCPIP; uid=my_user;pwd=my_pass"
    'CHANGE THE BELOW QUERY STRING ACCORDING TO YOUR NEED
    QRYSTR = "select * from PRTHD.STRSK_OH_EOO"
    Set DBCON = CreateObject("ADODB.Connection")
    DBCON.ConnectionString = DBCONSRT
    DBCON.Open
    'BELOW CODE USED TO GET THE DATABASE CONECTION AND EXECUTE THE QUERY CHANGE ACCORDIGN TO YOUR NEED
    Set DBRS = CreateObject("ADODB.Recordset")
    With DBRS
        .Source = QRYSTR
        Set .ActiveConnection = DBCON
        .Open
    End With    
End Sub

---

编辑：我已将我的代码更改为以下内容，但我仍然收到错误。错误是不能创建对象..可以帮助我..

---

I have changed my code to the following, but I'm still getting an error. The Error is "cant create Object"..Can ayone help me..

Dim DBCONSRT, QRYSTR As String

Dim DBCON  As Object

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;Initial Catalog=PRTHD;"

    DBCON = CreateObject("OLEDB.Connection")
    DBCON.ConnectionString = DBCONSRT
    DBCON.Open()
End Sub

推荐答案

我非常确定的JDBC功能不支持通过vba，我认为您需要使用ODBC连接器连接到DB2，如果您尝试将其集成到excel中。

The JDBC functionality I am pretty sure is not supported through vba and I think you need to use ODBC connectors to connect to DB2 if you are trying to integrate it into excel.

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连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！