append data from sep database

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Is there a way to append data from on Access database to
another? If so can someone please let me know how. I
know how to create Append queries from within a database
but I am not sure about how to do it between databases.
Also, if there is a way to automate this process that
would also help a whole lot.

Here is an example to help explain what I am trying to do.

THere are 2 databases:
DB1
DB2

Each of these databases contain the same table and field
structures. I am wondering if there is a way to Append
the data from the tables in DB2 to DB1.

The reason I am asking is bcause importing the data,
erases the data in the destination database before adding
the data. If there is a simpler way to add data to
tables from another database withouth erasing the
existing data I am open for suggestions.

Thanks a lot,
Chad
 
You can simply link the Table from DB2 to DB1 database and then use an
Append Query to append Records from the linked Table to the destination
Table.

Alternatively, you can simply use the IN Clause in your Append
Query to specify the database the Source Table is in. In this case you
don't need to link the Source Table.

Ro run it by code, you can use the OpenQuery Method or the Execute Method.

Check Access / Access VB Help on the above terms.
 
Van,

Thanks for the tips. I am just unsure about 1 thing. I
am familiar with the IN operator in SQL but am not sure
ho to use it to reference a seperate database. Do you
think you could give me an example of this?

thanks again,
Chad
 
No, not In Operator. You need the IN Clause. For example:

INSERT INTO tblLocalDest (DestField1, DestField2, ...)
SELECT SourceField1, SourceFfield2, ...
FROM tblExternalTable IN "SourcePath\SourceDatabase.mdb"

Check the Reserved word "In" in Help and select "In Clause".
 
Back
Top