Run-Time Error '3146' when inserting records into an Oracle Database over an ODBC Connection

Question

I'm working on my first Access application and I'm having trouble when I try to insert records from the Access database into an Oracle database.

The first record is inserted without error, but the second record raises a "Run-Time Error '3146'" error message.

Any ideas?

Private Sub Command42_Click()


    Dim dbMyDatabase As DAO.Database
    Dim rsMyRecords As DAO.Recordset

    Dim qdMyParameters As DAO.QueryDef

    Dim qODBC           As DAO.QueryDef
    Dim sql             As String
    Dim sqODBC          As String
    Dim sConnectDAO     As String
    Dim sVariable       As String
    Dim qdfTimeTrack    As DAO.QueryDef
    Dim qdfLocal        As DAO.QueryDef

    Dim sWorkDate   As String
    Dim sHours      As String
    Dim sCreateDate As String
    Dim sCreateUserId As String
    Dim sWmDbId     As String
    Dim sCsubDbId   As String
    Dim sComments   As String

    Set dbMyDatabase = CurrentDb

    Set qdfTimeTrack = CurrentDb.QueryDefs("QADB-INSERT")
    sConnectDAO = "ODBC;DSN=QADB;DBQ=QADB ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
    qdfTimeTrack.Connect = sConnectDAO
    qdfTimeTrack.sql = "set role all;"

    qdfTimeTrack.Execute

    Set qdfLocal = dbMyDatabase.QueryDefs("UpdateLocal")

    Set qdMyParameters = dbMyDatabase.QueryDefs("TimeRecordsForExport")

    Set rsMyRecords = qdMyParameters.OpenRecordset()

    Do While Not rsMyRecords.EOF

        sql = "INSERT INTO DAILY_JOB_ACTIVITIES ( DB_ID, PER_PER_DB_ID, CNUM_DB_ID, WORK_DATE, HOURS, CREATE_DATE, CREATE_USER_ID, WM_DB_ID, CSUB_DB_ID, COMMENTS ) " + _
            " values " + _
            " ( " + _
            " NIMS.DJA_SQ.NEXTVAL " + _
            " , 600178038 " + _
            " , '" & rsMyRecords(2).Value & "'" + _
            " , nvl(to_date('" & rsMyRecords(3).Value & "', 'MM/DD/RRRR HH12:MI:SS PM'), SYSDATE)" + _
            " , '" & rsMyRecords(4).Value & "'" + _
            " , nvl(to_date('" & rsMyRecords(5).Value & "', 'MM/DD/RRRR HH12:MI:SS PM'), SYSDATE)" + _
            " , '" & rsMyRecords(6).Value & "'" + _
            " , '" & rsMyRecords(7).Value & "'" + _
            " , '" & rsMyRecords(8).Value & "'" + _
            " , '" & rsMyRecords(9).Value & "'" + _
            " ); "

        MsgBox sql

        qdfTimeTrack.sql = sql

        ' Update QADB
        qdfTimeTrack.Execute

        ' qdfLocal.sql = "update TimeTrack set EXPORTED_FLAG = '1' where DB_ID = " & rsMyRecords(0).Value
        ' qdfLocal.Execute

        rsMyRecords.MoveNext

    Loop

    MsgBox "Records exported.  Remember to update refresh the internal database!"

End Sub

P.S.

I realize that I have no error handling yet. I'll add it once I understand how such a thing works in VBA. If you have comments about it, please post those as well.

Answer 1

I'd do the following to debug:

1. Take out the Execute statement that does the insert
2. Capture the first and second insert statements
3. Try running both statements in an SQL client and see what error is thrown by Oracle.