Lost Database Object

Ben Na
Ben Na used Ask the Experts™
on
I have a database with numerous tables, forms, reports, etc. While working on one of the forms, a contact (object) somehow got deleted from everything (cannot find any reference to it in any of the tables or reports where I know it was). I have a backup of the database, but I cannot find a way to import/copy all references of the contact throughout the database (without simply reverting to the backup, as I have made many other changes within the database that I would like to not have to redo). Is this possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
You should be able to easily "Export a database object to another Access database" - please see here how to do that:

https://support.office.com/en-us/article/Export-a-database-object-to-another-Access-database-4e2cd6dd-e482-441e-88b5-aa5319a428a6

Author

Commented:
Looked at that already. That works for the entirety of a table or report, but not for an individual object that has references within every table and report, unless I am mistaken.
lcohanDatabase Analyst

Commented:
Right - that link above will bring you the missing contact table structure and data back into your live database however in order to "I cannot find a way to import/copy all references of the contact throughout the database" - in my opinion you'll need to build those/add them back to existing code/forms/reports in the current database because if you just script all those from backup (or export/import via same method) you can  mess up existing stuff in it - right? I mean you can only go that far if you don't have all code/relations/etc. scripted and stored outside your DB just so you can do a diff and see what's missing to be able to add that portion back.
Distinguished Expert 2017

Commented:
Are you talking about a record with "child" records in other tables?  Or are you actually talking about an object such as a table, form, or report?

Before recovering the data, you might want to review the schema and remove Cascade Delete from the relationship.  Cascade Delete is very powerful but it is not appropriate for all relationships.  You probably don't actually want to delete a contact if the contact is referenced anywhere else.  Instead, you allowed the contact to be deleted and then cascaded the delete to all related tables.

Once the relationship is fixed, you will need to create a query for each table.  The only way you can add a row to a table and force it's PK to be populated with a specific value is via an append query.  Start with the highest level table and append the deleted record.  Then append the related data from each child table, one at a time.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial