VBA and DB2 connection

jeyanthb · #1 (**permalink**) 03-08-11, 17:44

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..

singhipst · #2 (**permalink**) 03-08-11, 23:10

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

jeyanthb · #3 (**permalink**) 03-09-11, 10:26

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...

Marcus_A · #4 (**permalink**) 03-09-11, 11:15

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

jeyanthb · #5 (**permalink**) 03-14-11, 02:29

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 ????

singhipst · #6 (**permalink**) 07-19-11, 00:40

Yes,

This driver has to be installed in local machine where you are executing your VBA/Excel macro.

Mithun0785 · #7 (**permalink**) 02-14-12, 17:08

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

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech