Needing to insert records to table from another table

[emhill57]

This is a strange one. Have a database with a front-end and backup-end (where the tables reside). A tech installed Access on a new person's laptop and somehow pointed the backend to the wrong folder. Don't ask me how.

That user enter 750 records into a table on the wrong back-end. I now need to get that data merged back to the live back-end table. I have tried append queries but it will not add any of the 750 records back to the correct table. Maybe the ID field is showing as duplicate so it will not append it.

Any ideas on this?

Thanks!

[orange]

This is a strange one.

Very true! How can someone using FE connect to the wrong/unintended BE? Something in the software to validate the BE or no testing of the procedure was done. Could be a tech made an error, but appears you are in need of some standard operating procedures.

Do you know when those records are located now?
Is there any audit trail or other reference as to which records were added by this user?
Are the table structures involved identical?

You may be able to work with a known front end and link to both of the BE databases. Some testing and selection of the records of concern should/could be done. Once you have a procedure - tested and output validated - you may be able to move the data to the proper table; then remove those records from the "wrong table and BE" or mark them as logically deleted.
You should do a Backup of each of the backends --just in case.

Don't panic -- many of us can appreciate the situation.


You might get more specific responses with some code/advice if you provide details.

[Gicu]

Are the IDs used s foreign keys in other related tables? If no then it is just a matter of appending to the "live" table all fields but the ID (assuming the ID field is autonumber). If yes then it gets a bit more complicated:
Add a field to the target (live) main table called Orig_ID or similar; append all fields like before but now append the ID (for the 750 records) to the empty Orig_ID field. Bring in the related tables and create queries where you join the related ID foreign key with the Orig_ID and append the related "live" table but making sure you use the newly created final "live" ID as the foreign key.
Cheers,

[emhill57]

Are the IDs used s foreign keys in other related tables? If no then it is just a matter of appending to the "live" table all fields but the ID (assuming the ID field is autonumber). If yes then it gets a bit more complicated:
Add a field to the target (live) main table called Orig_ID or similar; append all fields like before but now append the ID (for the 750 records) to the empty Orig_ID field. Bring in the related tables and create queries where you join the related ID foreign key with the Orig_ID and append the related "live" table but making sure you use the newly created final "live" ID as the foreign key.
Cheers,

I've tried the append from a table with the 750 records with no ID but it doesn't do anything. No error just doesn't append the data.

[emhill57]

Very true! How can someone using FE connect to the wrong/unintended BE? Something in the software to validate the BE or no testing of the procedure was done. Could be a tech made an error, but appears you are in need of some standard operating procedures.

Do you know when those records are located now?
Is there any audit trail or other reference as to which records were added by this user?
Are the table structures involved identical?

You may be able to work with a known front end and link to both of the BE databases. Some testing and selection of the records of concern should/could be done. Once you have a procedure - tested and output validated - you may be able to move the data to the proper table; then remove those records from the "wrong table and BE" or mark them as logically deleted.
You should do a Backup of each of the backends --just in case.

Don't panic -- many of us can appreciate the situation.


You might get more specific responses with some code/advice if you provide details.

We split the database to using a front-end and a back-end (with the tables). We then put the back-end onto a NAS drive and the front-end onto each users own PC. The tech somehow screwed up and loaded the old database (that was NOT split out) onto one user's PC. That user entered the 750 records into a specific table but it was only local to her PC. Not the back-end data. I have isolated the records I need to append to the back-end table but when I run the append query (using my source table WITHOUT the ID) I get no errors but no data is appended....

[Welshgasman]

If you do it manually, the GUI will tell you why?

[CarlettoFed]

If possible you should attach the two files to be able to carry out tests.

[Gicu]

Maybe at least upload a sample file with an empty copy of the target table so we could have a look, I suspect you might have some text fields with Required=True.

Cheers,

[CarlettoFed]

You may need both files.

[Gicu]

According to the OP they should be the same.

[CarlettoFed]

This is not the case, in both there are data that are not on the other since the operators worked on two databases in two different positions so it is necessary to verify why it is not possible to queue them.

[Gicu]

True, but I am simply talking about the empty table(s) that should be identical, I don't think it is reasonable to expect from the OP the real data for both tables and to remove any sensitive\private info might involve some time...

[Cottonshirt]

if you are still trying to solve this, I would export the 750 records from the FE to an Excel spreadsheet, then use that to upload them to the correct BE.


good luck with your project,


Cottonshirt

[orange]

???Why have we not heard from the OP?

[Micron]

@orange, perhaps cross posted and solved elsewhere?
What I didn't see suggested is to create a SELECT query first and if it returns the records, convert it to an append query (without any autonumber field of course). The issue may be that the append query selects no records to append.