Importing Data from Mainframe DB2 into Access

sarav_cit · #1 (**permalink**) 10-25-11, 14:56

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****nSQL (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

dr_te_z · #2 (**permalink**) 10-26-11, 03:50

Quote:

Originally Posted by sarav_cit

I am able to connect to the DB2 table successfully using OLE DB

That's the MS-approach. Try to make an ODBC connection using db2 commands. That's the IBM DB2 approach.
Who knows ...

goldfishhh · #3 (**permalink**) 10-26-11, 13:13

Better yet, here is a little code snipped I use to do nearly the same thing. I replicate data from MF DB2 into Access.

Just modify the queries to quit your needs.

Code:

Option Explicit

Dim adoDB2Connection, adoDB2RecordSet
Dim adoJetConnection, adoJetCommand, strDBPath
Dim objScript, strPathToMDB, strTempDB
Dim SQLSelectStatement

Const adCmdText23996 = &H0001

' Specify Access database file.
strDBPath = "D:\InetPub\WWWRoot\website.mdb"

' Open connection to DB2 database.
Set adoDB2Connection = CreateObject("ADODB.Connection")
Set adoDB2RecordSet = CreateObject("ADODB.Recordset")
adoDB2Connection.ConnectionString = "Provider=MSDASQL; DSN=DM1Q; UID=myuid; PWD=mypass;"

adoDB2Connection.Open


SQLSelectStatement=" SELECT * FROM DGT02T.PROD_STAT  "

adoDB2Recordset.Open SQLSelectStatement, adoDB2Connection

' Open connection to MS Access database.
Set adoJetConnection = CreateObject("ADODB.Connection")
adoJetConnection.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);" _
    & "FIL=MS Access;DriverId=25;DBQ=" & strDBPath & ";"
adoJetConnection.Open

' ADO command object to insert rows into Access database.

Set adoJetCommand = CreateObject("ADODB.Command")
Set adoJetCommand.ActiveConnection = adoJetConnection
adoJetCommand.CommandType = adCmdText23996

' Read the Db2 Data.
Do Until adoDB2Recordset.EOF
    ' Insert a row into the Access database.

 
    adoJetCommand.CommandText = "INSERT INTO ProdJobAbend " _
        & "(ID, [DATE], [TIME], SYSID, JOB, PROGRAM, STEPNAME, APPLICATION, PGMPSNAM, TERMCODE) " _
        & "VALUES (" _
            & "'" & adoDB2Recordset.Fields("PROD_ABEND_ID").Value & "', " _
            & "'" & adoDB2Recordset.Fields("DT_DT").Value & "', " _
            & "'" & adoDB2Recordset.Fields("TM_TXT").Value & "', " _
            & "'" & adoDB2Recordset.Fields("SYS_ID_TXT").Value & "', " _
            & "'" & adoDB2Recordset.Fields("JOB_TXT").Value & "', " _
            & "'" & adoDB2Recordset.Fields("PGM_TXT").Value & "', " _
            & "'" & adoDB2Recordset.Fields("STEP_NM_TXT").Value & "', " _
            & "'" & Replace(adoDB2Recordset.Fields("APPLCTN_TXT").Value, "'", "-") & "', " _
            & "'" & adoDB2Recordset.Fields("PGM_NM_TXT").Value & "', " _
            & "'" & adoDB2Recordset.Fields("TERM_CD_TXT").Value & "')"
    adoJetCommand.Execute

    adoDB2Recordset.MoveNext
Loop

' Clean up.
adoDB2Recordset.Close
adoDB2Connection.Close
adoJetConnection.Close

Quote:

Originally Posted by dr_te_z

That's the MS-approach. Try to make an ODBC connection using db2 commands. That's the IBM DB2 approach.
Who knows ...