Importing DB2 table into Access VBA

[sarav_cit]

Hi,

I have a requirement to data mainframe DB2 table and compare it with the other table stored in same access file. I am able to connect to the DB2 table successfully using OLE DB and trying to use ‘SELECT INTO’ phrase to create another table in the same access file so that I can compare those two tables.

Below is the code I used: DB2TEST is the Schema and DB2TBLE is the DB2 table, Newtable.

strSQL = " SELECT * " & _
" INTO Newtable FROM DB2TEST.DB2TBLE ; "
DoCmd.RunSQL (strSQL)

When I run this, the system does not recognize this as DB2 table and I am getting the error message 'Could not find file C:\Documents & Settings\..\DB2TEST.mdb'.

At the same time below code is working and records are imported into record set.

objCmd.CommandText = " SELECT * FROM DB2TEST.DB2TBLE ; "
objCmd.CommandType = adCmdText
Set objRs = objCmd.Execute

I can go for using a recordset, but the table is a huge in size (1.2 million records) so the performance will be very slow and I dont know record set can handle those many records.

I am stuck in here for the past few days. Your help be greatly appreciated.

I posted this under Queries, now posting it here.

Thanks in Advance.
Sarav

 

[jdraw]

Here's a link that may help.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0605bhogal/index.html