Difference between import and link

[Guest]

What is the difference?
If I have several different types of tables (Paradox,
Visual Foxpro, Oracle) and want to run a query to populate
tables in Access 2000 from these other tables, which is
the better method to use? I want the query to take the
shortest amount of time, of course.

thank you!

 

[Graham R Seach]

Your question is related to the difference between *importing* and *linking*
tables, and thus, whether the sources tables are in Paradox, VFP or Oracle,
is largely irrelevent.

When you *import* a table from a remote database, you create a copy of it in
Access. When you *link* an external table, you create a pointer to the
table, which still resides in the remote database.

In principle, accessing data from a local table will always be faster than
accessing the same data from a linked (or other remote) table. Having said
that, you might not always be able to import indexes when importing a remote
table. If you link to a database server (like SQL Server or Oracle), you can
issue queries/stored procedures that execute *on the server*, and thus will
usually run faster. Network performance will also play a role in determining
which is faster.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Thank you, Graham.

I'm trying to speed up the process (which at the present
time is taking about 30 minutes) to create a table in
Access from two Paradox tables (I'm running an append
query on linked tables). The tables are all on the local
computer. The Paradox tables have about 80,000 records. It
doesn't seem that it should take that long. Any ideas of
how I may speed it up?

Thanks!

 

[Graham R Seach]

All I can think of is, if the table structure is the same every time you
import the data, create the table in Access first, then import the data.
Keep the number of indexes (in the Access table) as low as possible, because
having to build indexes during the import will certainly slow the process
down. Also, if possible, avoid aggregate or recursive functions in the
import SQL.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html