Appending large recordset from Oracle Linked table

[Guest]

I have a Oracle linked table that I need to copy/append the data into a local
Access table.

I do not have access to the Oracle side of things. This is very large table
and When I try to append the records it seems to never finish - I am running
the process over night and it seems to hang up on this process. The Oracle
table can contain over 650K records . My client wants the data in a local
Access data warehouse so they don't have to go against the server 3000 miles
away.

I have tried the linked table via a query to append the data into the local
table - to slow, I tried limiting the data - and appending data - not much
different in time.

I tried Transferdatabase - not much difference.

Are there any other options to speed up the process - because I have other
tables to incorporated into my data warehouse.

Please note Access is my only tool of use right now.

Thanks,
Karen

 

[Guest]

How big is the Access database file? If it's getting near 2GB, as you are
importing a lot of records, that could cause problems. Try importing into a
new database or doing a compact and repair first.

Any error messages? Oracle varchar(2) fields can hold a lot more data than
an Access text field. If able to connect to Oracle, you could use the query
below to see if any fields are set above 255 or one of the LOB fields which
would give you trouble.

SELECT owner,
table_name,
column_name,
data_type,
data_length
FROM all_tab_columns
WHERE table_name = 'PERS'
ORDER BY 1, 3;

If the Access table has any indexes, you might want to remove them before
the import as that could speed up things.