Solved

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

Posted on 2004-08-10
8
1,208 Views
Last Modified: 2010-05-18
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 -www.benthicsoftware.com), 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
            rstPP.AddNew
            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
            rstPP.Update
            rstSrc.MoveNext
        Loop
        rstTblLst.MoveNext
    Loop
..."
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...
0
Comment
Question by:BrentTemple
  • 4
  • 4
8 Comments
 
LVL 9

Expert Comment

by:damerval
ID: 11768228
" I'm trying to use Access to migrate a subset of our Production Oracle DB to a Training Instance.  "

WHY???
0
 
LVL 9

Expert Comment

by:damerval
ID: 11768259
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.
Philippe
0
 
LVL 3

Author Comment

by:BrentTemple
ID: 11768333
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.
0
 
LVL 9

Expert Comment

by:damerval
ID: 11768371
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.

Philippe
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 3

Author Comment

by:BrentTemple
ID: 11768940
Philippe,

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...  

Thanks
0
 
LVL 9

Accepted Solution

by:
damerval earned 400 total points
ID: 11775937
Brent,

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
0
 
LVL 3

Author Comment

by:BrentTemple
ID: 11777443
Philippe,

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.

-Brent

0
 
LVL 3

Author Comment

by:BrentTemple
ID: 11779183
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)

-Brent
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now