Roger:
1.) To do this the easiest way is to link the tables from on db into the
other so as an example you'll end up with "Orders" and "Orders1" which is
the linked table.
Now, assuming that there is some unique primary key in the tables (other
than an autonumber which will be useless in this case) that contain the data
to be merged, you can start by identifying which records are unique in each
table ie. order and orders1.
The simplest way to do this is to create two select queries. In the first,
you include both Orders and Orders1 and create an outer join between them
based on their primary key fields so that the query will show all records
from Orders and only those records in Orders1 that match. Drag the * field
from Orders onto the query grid. Then add the primary key fields from
Orders1 to the query grid but don't output them. In the conditions row,
for the primary key fields from Orders1, set the condition to Is Null. What
will show up is the records in tblOrders that are Not in the linked table
Orders1. Then, do the same in reverse for Orders1 and Orders so you get
the unique records there.
Last create an two append queries, one each respectively that use as their
input basis the previous queries to append the missing records from one db
to the other.
2.) Now, with all that said and once you have the data synched between the
dbs. STOP. You shouldn't have gotten into this place originally. What
you need to do is to split your database between a front end that contains
only the forms, reports, queries etc. and a backend, that contains only the
data tables. You can use Access Database splitter (Access 2000 and above on
the Tools menu) to do this for you. Then place the front end db on each PC
and the backend data tables db either on a server or only on one PC. Have
both front ends link to the same backend data tables. That way the user
from each machine will update a common set of data and you avoid the mess
you have now entirely.
