MS Access inserting into Oracle Database Crashing Access. - ADO ODBC

I'm trying to use Access to migrate a subset of our Production Oracle DB to a Training Instance.  

I'm using ADO in VB, MS Office xp. Oracle 9i, and the Oracle ODBC Drivers that came with the 9i client.

I'm able to retrieve the data from Oracle into Access, with matching tables, and now I'm trying to export it to another Oracle database.   What is happening, is that after successfully exporting the data for several of the tables, it is crashing on updating one of the tables.  The crash is the generic Microsoft: "Microsoft Access has encountered a problem and needs to close.  We are sorry for the inconvenience." ...and allows me to submit the problem to MicroSoft.   I've applied all the Updates available for Access and the problem still occurs.

Also, My first thought was that I was attempting bad data, nulls or something...  I copied the data from the table and was able to add it to Oracle using Golden (SQL Tool, so I'm not sure the data itself is to blame.

The crash occurs on rstPP.update, 5th from the last line in the attached code.
    Set conn = New ADODB.Connection
    conn.Open "ODBC;UID=" & uid & ";PWD=" & pwd & ";DSN=" & DSN
    'Open recordset for list of tables to export
    strSQL = "SELECT TABLENAME FROM WorkingTables"
    Set rstTblLst = New Recordset
    rstTblLst.Open strSQL, CurrentProject.Connection
    Do Until rstTblLst.EOF
        Set rstPP = New ADODB.Recordset  'Oracle Destination table
        rstPP.Open "SELECT * FROM " & rstTblLst.Fields("TABLENAME"), conn, adOpenDynamic, adLockOptimistic
        Set rstSrc = New ADODB.Recordset 'Access Source table
        rstSrc.Open "SELECT * FROM " & rstTblLst.Fields("TABLENAME"), CurrentProject.Connection
        Do Until rstSrc.EOF
            For col = 0 To rstPP.Fields.Count - 1
                Debug.Print rstTblLst.Fields("TABLENAME") & ":" & rstSrc.Fields(col).Name & "->" & rstSrc.Fields(col) & "<-"
                rstPP.Fields(col) = Nz(rstSrc.Fields(col), " ")
            Next col
Other information:
The tables in Oracle and Access are 'matched' as follows:
Oracle CHAR(12) => Access Text (Field Size 12)
Oracle NUMBER(12,2) => Access Number (Field Size: Decimal, Precision 12, Scale 2)
Oracle NUMBER => Access Number (Field Size: Double)
This table doesn't have any Oracle LONG or LOB columns.

I need to figure out how to get this data from the Access tables to the Oracle tables in VBA...
Who is Participating?
Philippe DamervalConnect With a Mentor Senior Analyst ProgrammerCommented:

You may be 1 step away from making it work in Access but that's a major step and everything I have learned in my 12-year database career has taught me to know when to give up on a bad idea... anyway, my next guess is, the Oracle driver has a bug that prevents it from fully or correctly supporting the .Addnew and .Update methods on the recordset object. It is literally impossible to find out what this might be but I know that a generic error like "Access has encountered a problem and will now close" is always a question of some module or dependency not being the right one. Since you say all updates have been applied, I will conclude that this is irreversible. There's probably one specific version of the Jet which work socrrectly with the Oracle 9i drivers and good luck finding out which one.
By the way, the code you pasted may still be trying to assign a value to the Rownum field because you are using the Field object on the rstSrc recordset to assign the name. The driver knows not to present you with that in the structure - and indeed, it's not part of the structure but part of the runtime data - but it's still a resulting field which is part of the fields collection.
Another problem you may be experiencing is that the locking policy on your recordset (which I see is dynamic and optimistic) may be incompatible with Oracle tables. Locking and dynaset mode are only "operational" on Access tables which are ruled by file system parameters, as opposed to Oracle which are ruled by queued reads and writes. So, you may not have permissions to perform the kind of lock that the ADO driver translates "dynamic" and "optimistic" to be on the Oracle side of things. There is simply no way to know.
So, I suggest you try a different approach: Use the sql INSERT statement. I use it principally when I need to insert data from  a program. Use the fields collection to gather an array of values for your source dataset, debug.print it to verify that it is what you need, then issue an INSERT statement to a pass-through query (you can use a pass-through query created prior to the operation, or create one dynamically using the querydefs collection) with the values obtained from the source object. That SHOULD work.

Philippe DamervalSenior Analyst ProgrammerCommented:
" I'm trying to use Access to migrate a subset of our Production Oracle DB to a Training Instance.  "

Philippe DamervalSenior Analyst ProgrammerCommented:
Sorry, after gasping for air, here is my question: Why not simply use the Oracle export-import facility directly? It seems like quite a convoluted operation to do something fairly simple. You also could directly copy tables from one instance to the other simply by issuing DDL and DML. I have often done copies of entire or partial databases in that way.
Tell me more about your training instance. Is it on a separate database? On a separate server? If so, you may want to actually put it on the same server as the production one, it will greatly simplify things. But you don't have to. At any rate you really don't have to use Access or anything else but the regular Oracle tools.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

BrentTempleAuthor Commented:
Yes...   I do see your point.   The problem that I'm trying to work around is that it is a huge database, so I need a small(er) subset of data.   But I can't just export based on something simple like timestamps, because I want a set of data with relational integrity; If object A references object B, I want to get all of the tables for Object B.   Then Object B might reference Object C or D etc. (Objects being a business object such as Work orders, Purchase Orders, Contracts, Receipts, Invoices, Routing Lists, etc.)

I've built a lot of processing in the Access database to determine where all the object references are.   If I start with about 10 seed objects, after all the analysis is done, I may end up with about 1,000 business objects that are interrelated.  

The access database has additional tables that track all the relationships and tables that need populated, and from these tables, it pulls down the records that I want to put in the Training database.  

The training instance is on the same server.
Philippe DamervalSenior Analyst ProgrammerCommented:
If the training instance is on the same server, then no hesitation is possible:

1. Create mirror tables using DDL (You may want a SQL management tool like RapidSQL or SQL Navigator ir Aqua Data Studio)

2. Create views that select the data you want

3. Create more views to copy the data from those views to the new instance.

No matter how much processing you have already built in the Access database, it will take longer to stick with Access than to do everything directly in Oracle.

My guess, about the failing code, is that the ODBC driver doesn't know how to handle rownum columns. You are accessing the fields by index and I strongly suspect that your column 0 is a rownum type colum (used for partial selections among toehr things) which Access doesn't recognize or deal with. But that's just a guess.

BrentTempleAuthor Commented:

I can see your strategy if either the data were more simple, or my knowledge of PL SQL was a lot more.   I can't do this with simple SQL..  Right now, I run several iterations to retreive data, check for other objects and then retrieve more data.   The relationships and keys are all table driven, and the SQL is built in VBA.

It might be easier for you to do it directly in Oracle, but (perhaps due to my skill limitations), I can't get past that I'm 1 step away from making it work in Access, and I don't see an easy way to rebuild in Oracle.

I like your guess about the rownum...   I modified the syntax to use the Name from the Access Table, which I know doesn't use a rownum column.  Unfortunately that didn't work either.

            For col = 0 To rstPP.Fields.Count - 1
                'If col > 0 Then strSQL = strSQL & ", "
                Debug.Print rstTblLst.Fields("TABLENAME") & ":" & rstPP.Fields(col).Name & "->" & rstSrc.Fields(col) & "<-"
                rstPP.Fields(rstSrc.Fields(col).Name) = Nz(rstSrc.Fields(col), " ")
            Next col

While my reasons for wanting to do it are questionable :-)   Even if I dropped my Access Idea and used Oracle, I would still want to know why I'm not able to insert a row in Oracle via ADO...  

BrentTempleAuthor Commented:

Thanks for your help...    As you don't think that the ADO/ODBC bridge will work, I'll discontinue it.  It's frustrating, because I'm not doing anything extra-ordinary (like CLOB or LONG data).  I'm simply doing an update.  

I'm going to try building the SQL INSERT statements idea.


BrentTempleAuthor Commented:
One last comment for anyone that is looking up a similar problem to this:

I was able to successfully move the data from Access to Oracle using VBA to build an 'INSERT SQL' statement as Philippe suggested.

I did have to check my strings for apostrophes (converted to double-apostrophes) and special characters to make the SQL statement work.  (any character < chr(32) or > chr(126) was converted to a space)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.