Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    5

    Unanswered: VBA and DB2 connection

    Hi,

    I am very new to Db2 and also VBA to an extent. Could somebody help me out with below request.

    I request somebody to help me with a sample code.... Say I have a table in db2...

    Name and Employee Id

    Now I have the details in Excel.... Say in sheet1.. Column A is Name and Column B is Emp ID

    I require a code with which I can make a connection to my database and push the data to my tables in db2....(Probably with some explanation like code breakdown @ important palces)


    Please help me with this request.. Thousand thanks in advance....

    I am not very sure If I posted in the right place.. My Apologies If I had posted in wrong forum..

  2. #2
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    Modify the code according to your connection and query.

    Code:
    Option Explicit
    
    Dim DBCONSRT, QRYSTR ans As String
    Dim DBCON, DBRS  As Object
    
    Dim excel_app As Object
    
    Private Sub CommandButton1_Click()
        Set excel_app = CreateObject("Excel.Application")
    	DBCONSRT = "Driver={IBM DB2 ODBC DRIVER};Database=<DATABASE NAME>;hostname=<HOSTMACHINENAME>;port=<PORT_NUMBER;protocol=TCPIP; uid=<USER_ID>; pwd=<PASSWORD>"
    	'CHANGE THE BELOW QUERY STRING ACCORDIGN TO YOUR NEED
    	QRYSTR = "select B.TBNAME as tabname,count(B.NAME)as colcount from SYSIBM.SYSTABLES A,SYSIBM.SYSCOLUMNS B WHERE A.NAME=B.TBNAME AND A.CREATOR=B.TBCREATOR AND A.TYPE='T' AND  B.TBCREATOR='<OWNER_NAME>' group by B.TBNAME"
    	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
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  3. #3
    Join Date
    Feb 2011
    Posts
    5
    Thank you very much.. this is really helpful... I have couple of other questions

    With the below code I am establishing my connection to db.. Correct ?? Then should I just put in the remote machine details If I am connecting to someother db on a remote server

    Code:
        Set excel_app = CreateObject("Excel.Application")
    	DBCONSRT = "Driver={IBM DB2 ODBC DRIVER};Database=<DATABASE NAME>;hostname=<HOSTMACHINENAME>;port=<PORT_NUMBER;protocol=TCPIP; uid=<USER_ID>; pwd=<PASSWORD>"
    	'CHANGE THE BELOW QUERY STRING ACCORDIGN TO YOUR NEED
    	QRYSTR = "select B.TBNAME as tabname,count(B.NAME)as colcount from SYSIBM.SYSTABLES A,SYSIBM.SYSCOLUMNS B WHERE A.NAME=B.TBNAME AND A.CREATOR=B.TBCREATOR AND A.TYPE='T' AND  B.TBCREATOR='<OWNER_NAME>' group by B.TBNAME"
    	Set DBCON = CreateObject("ADODB.Connection")
        DBCON.ConnectionString = DBCONSRT
        DBCON.Open
    Code:
        '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
    Can you help me with this part ?? Can you explain how the query is executed and how do I write the data to the excel cells ??

    Finally, Guys Is there a book or something.. where I can read all these... I am sure these are covered in somebooks but I am not aware of which one.. please help me out...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would check out the Developing ADO.NET and OLE DB Applications manual from this site:
    https://www-304.ibm.com/support/docv...id=swg27015148
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2011
    Posts
    5
    Now that I understand how the connection is working... Here is my another question...

    In the above connection I am using IBM DB2 Oledb Driver.... Is it necessary that the user who is using the excel in another has to have the driver installed on his machine as well ????

  6. #6
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    Yes,

    This driver has to be installed in local machine where you are executing your VBA/Excel macro.
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  7. #7
    Join Date
    Feb 2012
    Posts
    1

    Need help

    Code:
        '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
    can you help me modify this to write this to a excel column directly? i am confused on how does the query work. please help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •