Solved

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

Posted on 2004-08-10
8
1,194 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
Comment Utility
" 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 3

Author Comment

by:BrentTemple
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

16 Experts available now in Live!

Get 1:1 Help Now