A user made a mistake and deleted the wrong property from our Access 2003 MDB database. We do have prompts and warnings about the action but he proceeded anyway, thinking he was removing the correct entry.
He has asked for help in recovering the lost data. There really is no way to recover it all by entering it manually.
The delete function goes thru the entire DB eliminating all traces of the deleted item from a total of 15 files.
All files in the DB have an autonumber primary key. The primary can be used as a link other files in this set of 15. So my objective was to reinstate the data with the same primary keys.
I created a 2003 MDB with the current 'good' version of the 15 files and the pre-delete 'old' version of the same 15 files linked in.
I ran a compact repair on both the current and old versions of the MDB.
I then created this routine:
Public Sub recoverBRT(passedOutputTable As String, passedInputTable As String, passedWhereClause As String)
Dim insertString As String
insertString = "INSERT INTO " & passedOutputTable & " SELECT * FROM " & passedInputTable & passedWhereClause
which is called 15 times from another routine that passes the appropriate table name and 'Where' condition. Examples form the other routine:
wkGoodPropertyID = 2856144
recoverBRT "tblProperty", "tblProperty_Old", " Where ID = " & wkGoodPropertyID
recoverBRT "tblEvents", "tblEvents_Old", " Where PropertyID = " & wkGoodPropertyID
I ran the routine to completion then compacted the 'good' backend mdb that just had the data -reinserted.
I opened the front end mdb with the 'good' backend mdb linked.
Everything appears to be fine. All the records I inserted are linked and flow correctly. Using the respective forms I added new (payment, comment etc..) records to this property and everything appears to work fine.
I also did maintenance and additions for other property records. It all seems to work.
I used this technique to copy entire tables when the ID have to be maintained. That statement looks just like the above without the 'Where' clause. But I've never done a partial reinsertion.
My question: Is there something I'm missing or should be testing more thoroughly? I've been working with Access for decades but can't think of a single time I tried reinserting records keeping the same ID.