access is on 2 computers how do i ccombine without loosing data

[hshapiro5]

am using acccess 2007 as 2003. i have made changes to database on one
computer while someone else did the same on another. howdo i combine data
without loosing anythiing?

 

[Golfinray]

Is this on a LAN? Peer to Peer? You can use File/get external data/link to
get items from one to the other.

 

[hshapiro5]

it is in two seperate locations

Is this on a LAN? Peer to Peer? You can use File/get external data/link to
get items from one to the other.

 

[Ken Sheridan]

If its just the data that's involved and the table structures are the same
then you can link to the tables in the old database and append rows into the
same tables in the new.

However, it might not be quite so straightforward as it sounds. At the most
basic level you could have simple conflicts, e.g. the same new Contact has
been entered into a Contacts table in each. If you've used autonumber
primary keys then, unless you've also indexed the names uniquely (which would
be unwise as names can be duplicated) they would be treated as separate
people, so you'd end up with a redundant row.

At a more complex level, if you've added rows in a table which is on the
'many' side of a one-to-many relationship, then its foreign key value might
not reflect the primary key of the referenced table in the other database.

These sort of problems can be handled, but its not a trivial task as it
involves executing a series of 'append' and 'update' queries in a logical
order. I once had to design an application to do this on a periodic basis (a
sort of hand-crafted replication facility) for an admittedly complex database
containing 89 tables, and it involved executing 97 queries in a set order.
Now, I don't imagine yours will be anything like as complex. It may be quite
straightforward, but its not possible to say how simple or how complex the
task would be without.

If both users have access to a shared folder on a network you can, and
almost certainly should split the database into front and back ends, the
former containing the forms, reports queries etc. the latter just the tables.
The built in database splitter wizard can do this for you, creating links in
the front end to the tables in the back end. The back end can then be placed
in the shared folder and the links refreshed using the built in Linked Table
Manager. Both users will then be using the same tables, so any changes made
by one are automatically available to the other.

Ken Sheridan
Stafford, England

 

[hshapiro5]

thanks i'lldo what you suggested

If its just the data that's involved and the table structures are the same
then you can link to the tables in the old database and append rows into the
same tables in the new.

However, it might not be quite so straightforward as it sounds. At the most
basic level you could have simple conflicts, e.g. the same new Contact has
been entered into a Contacts table in each. If you've used autonumber
primary keys then, unless you've also indexed the names uniquely (which would
be unwise as names can be duplicated) they would be treated as separate
people, so you'd end up with a redundant row.

At a more complex level, if you've added rows in a table which is on the
'many' side of a one-to-many relationship, then its foreign key value might
not reflect the primary key of the referenced table in the other database.

These sort of problems can be handled, but its not a trivial task as it
involves executing a series of 'append' and 'update' queries in a logical
order. I once had to design an application to do this on a periodic basis (a
sort of hand-crafted replication facility) for an admittedly complex database
containing 89 tables, and it involved executing 97 queries in a set order.
Now, I don't imagine yours will be anything like as complex. It may be quite
straightforward, but its not possible to say how simple or how complex the
task would be without.

If both users have access to a shared folder on a network you can, and
almost certainly should split the database into front and back ends, the
former containing the forms, reports queries etc. the latter just the tables.
The built in database splitter wizard can do this for you, creating links in
the front end to the tables in the back end. The back end can then be placed
in the shared folder and the links refreshed using the built in Linked Table
Manager. Both users will then be using the same tables, so any changes made
by one are automatically available to the other.

Ken Sheridan
Stafford, England