How do I import records into access from another access database

[Guest]

Access 2000

I am trying to import records from one identical (in terms of structure)
database to another. Can I import individual or mutiple records (ie whole
tables)?

I have tried using File: Get external Data... Import. to import or join
tables. This has the effect of duplicating tables rather than adding new
records to the existing tables (eg I end up with two tables such as tbl_Fauna
and tbl_Fauna1).

What I want to do is add the records to the existing tables so that I end up
with one complete database.

 

[John Nurick]

Hi Gabe,

Use a linked table (File|Get External Data|Link) to get access to the
records in one database from the other.

Then use an append query to move the data from one table to the other.
To selct individual records or a selection of records, set criteria in
the query.

 

[Guest]

Thanks John that was helpful

In the interest of efficiency; can I append multiple tables to the
equivalent tables using one query or do I need to design a query for each
table?

Gabe

 

[John Nurick]

AFAIK you'll need to execute a separate query for each. But you can
short-circuit the process. Instead of first creating a linked table and
then an append query, you can work in the query designer's SQL view and
create an append query that gets its data direct from the external
database.

If the source and destination tables have the same name and same
structure (including field names) and you want to import all the
records, it's really simple, like this, with XXX being the name of the
table:

INSERT INTO XXX
SELECT *
FROM XXX IN 'C:\My Folder\subfolder\database.mdb';

So you can use a single query and modify it for the various table names.

If this is going to be a regular task, it's even possible to automate
the whole thing. You'd create a table in which to store the table names
to import, and then write VBA code that works through the list of names
assembling and executing the appropriate SQL statements like the one
above.