Transfer data from Oracle to Access

[Kent Prokopy]

Can some one tell me the best way to transfer data from Oracle to Access
using ADO?

I have tried two ways so far and am not too happy with the
speed/performance.

1) I have done a "select * from" and save the results as a CSV file. Then
do an "Insert into () Select * from CSV"

2) I have done a "Select * from" and then loop through the results and
insert one record at a time.

The first method is slow.
The second method in very slow.

Thank you in advance.
Kent Prokopy

 

[Ron Weiner]

The easiest way I think would be to execute a query that did it all like:

INSERT INTO tblNewAccessTable ( AccessField1, AccessField2, AccessField3,
etc.) SELECT OracleField1, OracleField2, OracleField3, Etc. FROM OracleTable
WHERE whatever makes sense here;

Also you may find that dropping all of the Access tables Index's before the
big insert might speed things along. Of course you'll need to recreate them
when the insert is done.

Ron W

 

[Kent Prokopy]

Ron,

When you say OracleField1... What are you referring too? Is this an
adoRecordset? and if so should it be OracleFiled(0).Value.
Or is this some how the Oracle Table itself?

 

[Ron Weiner]

I am referring to the name of the fields in the Access and Oracle databases.
What you want to do is to execute an action query. You do not want to
iterate a recordset inserting one record at a time.

If the Oracle table(s) were already linked to your Access database via ODBC
then your code might look like:

strSql = "INSERT INTO tblNewAccessTable ( AccessField1, AccessField2,
AccessField3, etc.) SELECT OracleField1, OracleField2, OracleField3, Etc.
FROM OracleTable WHERE whatever makes sense here;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

You can find examples in Access help for the two different syntaxes of the
Insert Into Sql command. You can also find info on how to link or import
ODBC data sources in Access help. In fact once you have a link to the
Oracle table you may discover that you do not need to import data at all,
since you will have the live Oracle data available real time.

Ron W

 

[Kent Prokopy]

Thank you Ron

This may be what I end up using, but I did forget to mention one thing. I am
trying to do this without linked tables. I would like to use ADO with Server
side cursor. The results come back faster this way and there is a lot of
data being pulled back on a daily basis.

Thank you for your assistance.

Kent Prokopy

 

[Randy Harris]

Can some one tell me the best way to transfer data from Oracle to Access
using ADO?

I have tried two ways so far and am not too happy with the
speed/performance.

1) I have done a "select * from" and save the results as a CSV file. Then
do an "Insert into () Select * from CSV"

2) I have done a "Select * from" and then loop through the results and
insert one record at a time.

The first method is slow.
The second method in very slow.

Thank you in advance.
Kent Prokopy

Kent, I've done quite a bit of testing of different methods to accomplish
what you are doing. I too preferred to not use linked tables. Without
linked tables, I've found three effective methods. The books say that using
linked tables is faster, I've not found that to be the case, however. (Very
much to my surprise)

If you want a portion of a table from Oracle, there are 2:

You can use the syntax similar to:

INSERT INTO AccessTable (Field) SELECT Field FROM TABLE IN
""[ODBC;ConnectString]

It's not terribly fast, however, and the syntax is EXTREMELY fussy. Once
you get the syntax working, it is serviceable. (I use a public constant for
the "IN .." portion of it)

Iterating through the recordset is faster and easier. The performance is
virtually identical to selecting from a linked table and I NEVER have
problems with dropped links or timeouts. Open two recordsets, one on the
sending end, the other on the receiving. If the field names are the same in
both tables, I use a For Each for the fields, makes coding extremely easy.

If you want an entire table, far and away the fastest and easiest method is
TransferDatabase. Sometimes this can be problematic, however, as the data
types don't always match up and you don't get indexes. Depends on your
needs, of course.

If you're interested, I can post sample code.

HTH
Randy