none
Connect & Fetch Data from IBM DB2 with Excel VBA RRS feed

  • Question

  • Hi,

    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)

    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 'Variable for ADODB.Connection object

    Dim rs As Object 'Variable for ADODB.Recordset object

    Set conn = CreateObject("ADODB.Connection")

    Set rs = CreateObject("ADODB.Recordset")

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

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

    rs.Close

    conn.Close

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

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

    Database Manager – DB2 for linux, Unix and windows

    Thanks in advance!


    JPP

    Thursday, January 18, 2018 6:44 PM

All replies

  • Hi John P Parker,

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

    Using ADO to connect to an IBM DB2, Microsoft SQL Server or Oracle database

    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.

    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 

    Reference:

    DB2 connection from excel macro

    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.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 6:50 AM
    Moderator
  • Hi,

    As I run the code, I found Syntax Error message on the following line.

    Using connection = New OleDbConnection(DBCONSRT )

    Also, can you please let me know how to fetch the result set in the excel.

    Thanks!


    JPP

    Monday, January 22, 2018 8:12 AM
  • Hello,

    I think the code posted by Deepak is VB.NET, which will not work for VB6.

    However you could use the connection string as mentioned.

    in you code, you post: conn.Open "DB_Name", "Username", "Password"

    What is in the viariable DB_Name? Do you have a DSN mapped to this?

    Monday, January 22, 2018 8:54 AM
  • Hi Wouter,

    DB_Name is the database name from where I would like to fetch the data. I know the following details of my database:

    DB Name

    Table Name

    Host Name

    Port Number

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

    Database Manager – DB2 for linux, Unix and windows

    Could you please let me know how I should place them in the code and store the result in an excel.

    Thanks!


    JPP

    Monday, January 22, 2018 7:29 PM
  • Hi John P Parker,

    I am not available with the IBM DB2 Database.

    It is third party database for us.

    So whatever I find based on my search , I am not able to make a test that It is working correctly or not.

    All the information I find is available on IBM Site.

    So you can also raise this issue there and try to know about there suggestions.

    On my side, I find one work around that may work for you.

    You can refer the steps in link below.

    Import DB2 table data into a spreadsheet

    During following this steps, you can record the macro.

    So at the end , When you get the data in Excel then you can view the generated macro to get the code.

    Which you can use in future.

    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.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 23, 2018 8:56 AM
    Moderator
  • Hello John,

    following code works for me on DB2, I hope it those the same for you:

    Dim conn As Object 'Variable for ADODB.Connection object

    Dim rs As Object 'Variable for ADODB.Recordset object

    Set conn = CreateObject("ADODB.Connection")

    Set rs = CreateObject("ADODB.Recordset")

    conn.ConnectionString = "Provider=IBMDADB2.1;UID=YourUserId;PWD=YourPassword;Data Source=DB2;ProviderType=OLEDB"

    conn.Open

    rs.Open "Select * from YourTable", conn

    rs.Close

    conn.Close

    Change the words YourUserId, YourPassword, YourTable to what you need.

    Hope it helps

    Tuesday, January 23, 2018 4:17 PM
  • Hi Wouter,

    Thanks much for your help. I had to make some minor changes in order to run the code. Please find below the code that works for me.

    Dim conn As Object 'Variable for ADODB.Connection object
    
    Dim rs As Object 'Variable for ADODB.Recordset object
    
    Set conn = CreateObject("ADODB.Connection")
    
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.ConnectionString = "Driver={IBM DB2 ODBC DRIVER - TACOM30};UID=JPP;PWD=Password;Protocol=TCPIP;Hostname=Host Name;Port=10000;Database=DB_Name"
    
    
    conn.Open
    
    rs.Open "Select * from table_name", conn
        For iCols = 0 To rs.Fields.Count - 1
            Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
        Next
    
        Sheet2.Range("A2").CopyFromRecordset rs
    
    'ThisWorkbook.Worksheets("Sheet5").Cells(1, 1).CopyFromRecordset rs
    'Set rs = Nothing
    
    rs.Close
    
    conn.Close

    I have another question, just out of curiosity. Currently the above code works only with 1 database. Is it possible to get the record set for 2 database? Please note that the intent is to compare two tables hosted in 2 different database. Can we compare 2 different database in DB2 using Except?

    Since the original issue has been resolved, if you want I can open a new thread to address the above problem.

    Thanks again for your help.


    JPP

    Wednesday, January 24, 2018 1:52 PM
  • Hi John P Parker,

    You had mentioned that,"Since the original issue has been resolved, if you want I can open a new thread to address the above problem."

    I suggest you to mark the suggestion given by Wouter Defour as an answer before creating a new thread.

    It will help us to close this thread and It will help other community members in future, Who have same kind of issue like yours.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 25, 2018 7:31 AM
    Moderator