DB2 connection from excel macro

Question

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

---

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

Answer 1

Start with changing

DBCON = CreateObject("OLEDB.Connection")

to

Set DBCON = CreateObject("ADODB.Connection")

If you still get an error, double-check your connection string.

Answer 2

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