Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Importing Data from Mainframe DB2 into Access

    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
    Last edited by sarav_cit; 10-25-11 at 17:34. Reason: correction

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by sarav_cit View Post
    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 ...

  3. #3
    Join Date
    Nov 2004
    Posts
    54
    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 View Post
    That's the MS-approach. Try to make an ODBC connection using db2 commands. That's the IBM DB2 approach.
    Who knows ...
    Last edited by goldfishhh; 10-26-11 at 14:23.

  4. #4
    Join Date
    Oct 2011
    Posts
    3
    Thank you so much goldfish, it is very useful.

  5. #5
    Join Date
    Nov 2004
    Posts
    54
    hey - not a worry. That is what these boards are for.

    Dean-

    Quote Originally Posted by sarav_cit View Post
    Thank you so much goldfish, it is very useful.

Tags for this Thread

Posting Permissions

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