BrentTemple
asked on
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 -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("TABLENAM
Set rstSrc = New ADODB.Recordset 'Access Source table
rstSrc.Open "SELECT * FROM " & rstTblLst.Fields("TABLENAM
Do Until rstSrc.EOF
rstPP.AddNew
For col = 0 To rstPP.Fields.Count - 1
Debug.Print rstTblLst.Fields("TABLENAM
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...
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("TABLENAM
Set rstSrc = New ADODB.Recordset 'Access Source table
rstSrc.Open "SELECT * FROM " & rstTblLst.Fields("TABLENAM
Do Until rstSrc.EOF
rstPP.AddNew
For col = 0 To rstPP.Fields.Count - 1
Debug.Print rstTblLst.Fields("TABLENAM
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...
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
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
ASKER
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.
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.
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
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
ASKER
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("TABLENAM
rstPP.Fields(rstSrc.Fields
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
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("TABLENAM
rstPP.Fields(rstSrc.Fields
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
WHY???