Append ODBC tables into Access Table

[Ross]

To All,
I am ODBC connected to a Teradata table having about 10 fields and 300,000
records.

I want to append these records into my Access table and am having speed
problems, not being able to get this append under 4 minutes.

When I used a passthru query with an Access Append query on top of it, I got
the best, 4 min performance, but I would like to get this under 60 secs if
possible.

My question is, in general, what is the fastest way to load access tables
with data from a non-Access external database source?


Thank you

 

[Duane Hookom]

I think you might have the best solution short of possibly using SSIS in SQL
Server. I would try remove all constraints and indexes from the target table
to see if that makes a difference.

 

[Jeff Boyce]

Ross

Your 'given' is that the data has to be downloaded to your Access table (and
appended).

Why?

What will having a copy of the Teradata table in Access allow you to do that
you couldn't do if you simply connected to the Teradata table?

I ask in case the need you are trying to solve this way may also be solved
some other way...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Ross]

Duane,
No Luck with indexes and restraints. Performance was approximately the same.

Ross

 

[Ross]

Jeff,

I Need to have this data in an access table mainly for performance purposes.
The table in question will be used for many lookups by multiple users, all
day. The ODBC connection is tooooo doggy, particularly if the Access Db is
on put out on the network.

You are right and I agree, I would prefer not to bring it in but feel that I
have too.

Thank you

Ross

 

[Duane Hookom]

I recently had to perform something similar and it took too long. I added a
last updated field to the SQL table so I could filter out records that hadn't
been added or changed.