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
E"), conn, adOpenDynamic, adLockOptimistic
Set rstSrc = New ADODB.Recordset 'Access Source table
rstSrc.Open "SELECT * FROM " & rstTblLst.Fields("TABLENAM
Do Until rstSrc.EOF
For col = 0 To rstPP.Fields.Count - 1
E") & ":" & rstSrc.Fields(col).Name & "->" & rstSrc.Fields(col) & "<-"
rstPP.Fields(col) = Nz(rstSrc.Fields(col), " ")
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...