Importing Data from one Access DB to Another

[souchie40]

Hi everyone

Can anyone help me? I've created a macro that links a table from one
database and then runs a append query to insert the data into an exsisting
table in a second DB. Now this is fine but there will be problems, what I
would like to do really, is to update the destination table with the changes
made in the source table so that old programmes are deleted and any new or
changes to exsisting programmes become visible.

Many Thanks In Advance

 

[John Nurick]

If the idea is that the records in the destination table should match
those in the source table, you can modify your macro to

1) link the table
2) run a delete query to delete the existing records in the destination
table
3) run your append query to copy all the records in the source table to
the destination table.

But be sure to make backup copies of both databases before trying it.

If this isn't the answer, please post back in this thread with fuller
description of what you want to achieve.

 

[souchie40]

Thanks John,

That worked great, but if I've linked records in the destination table with
other tables in the destination DB then these records can't be modified is
there a way to do this?

MTIA

 

[John Nurick]

In that case you'll probably need to build three queries for your macro
to run:

1) An update query which updates fields in records that already exist in
the destination table with the values from the corresponding records in
the source table. (I.e. the query joins the two tables on their primary
key fields, an ordinary inner join.)
2) An append query to add the new records (i.e. those that exist in the
source table but not the destination table).
3) If you need to delete records from the destination table that have no
counterpart in the source table, a delete query (modified from a Find
Unmatched query).

 

[souchie40]

John,

Thank you very much that now woreks great

In that case you'll probably need to build three queries for your macro
to run:

1) An update query which updates fields in records that already exist in
the destination table with the values from the corresponding records in
the source table. (I.e. the query joins the two tables on their primary
key fields, an ordinary inner join.)
2) An append query to add the new records (i.e. those that exist in the
source table but not the destination table).
3) If you need to delete records from the destination table that have no
counterpart in the source table, a delete query (modified from a Find
Unmatched query).