Importing Mainframe DB2 table into Access

[sarav_cit]

Hi,

I have a requirement to import data from a 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'.

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 will be greatly appreciated.

Thanks in Advance.
Sarav

 

[jdraw]

Did you try linking to the db2 table from Access?


http://www.ibm.com/developerworks/data/library/techarticle/dm-0605bhogal/

 

[sarav_cit]

Thanks Jdraw, I have not tried this. Will try that out. But I am going to give this to Business people who do not know this technical stuff, and mainthing is I am going to automate the process, business person can just click one button in access/excel and it should display the discrepancy report, that is why I chose VB macro... looks like some manual work involved in this one...