Find the latest records on duplicate tables 1

[Aximboy]

I have created an access database in a shared drive so that employees can enter their production data, and the bosses to view the report.

One of the employee drag and drop this access file to his folder on his computer creating a duplicate database. He has entered over a thousand new record on this database (the one on his desktop).

How do find out which records are missing from the one on the shared drive? And how to I transfer those records to the one on the shared drive?

Please help!

 

[SkipVought]

Hi,

I would not reward bad behavior.

He's got to enter the data in the correct manner himself!

 

[dhookom]

This is one of the reasons why we recommend splitting your applications into front-end and back-end files. They can copy and/or delete their copy of the front-end without having any adverse issues with the back-end tables.

Without understanding your tables and relationships, it is impossible to suggest a strategy other than Skip's tough love advice ;-)

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

just link to both databases. Then do an outer joing linking his tables to your tables. Pick a field from your table and set the criteria to null. This will return all records in his table not in your table. Do an insert query. You will have to do this for all possible tables that could have inserts.

 

[Aximboy]

@SkipVought, I wouldn't either but my boss told me to fix his mess.

@dhookom, I'll do that next time.

@MajP, thanks for the tip, I'll give it a try.

 

[dhookom]

MajP's suggestion will work well for a simple database that doesn't use autonumbers and foreign keys in related tables. Again, without additional information about your tables and relationships, we can't provided much more guidance.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

So on Duane's point for all tables with autonumbers you will have to add a field on your table called something like "hisAutonumber" to store his old primary key. And when you do you insert query into your main table you need to put his autonumber into the "hisAutonmuber" field. Now you will have a new PK and a field with the old PK. You will need this old value to match with the new autonumber when you upate the child tables. Then to update the child tables you link on the "hisAutonumberField" the old primary key. Then you do an insert query into your child table, inserting the new primary key autonumber as the foreingn key in your child table. This is doable but as said you could have hundreds of tables with multiple autonumber fields. As people said. ALL Access multi user applications should be split into Front ends maintained on the local machine and a single back end on the server.