Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Properly Reinserting data that has been deleted, into an MDB

Posted on 2017-08-12
Medium Priority
Last Modified: 2017-08-13
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
DoCmd.SetWarnings False
insertString = "INSERT INTO " & passedOutputTable & " SELECT * FROM " & passedInputTable & passedWhereClause
'Debug.Print insertString
DoCmd.RunSQL insertString
End Sub

Open in new window

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

Open in new window

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.
Question by:mlcktmguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 42252402
Doesn't sound like you've missed anything, given that you report the database works after the "repair". You can insert into an AutoNumber field, which it sounds like you did. About the only thing that would cause concern would be ensuring the AutoNumber value does not already exist, but your code would have balked if so.

Author Closing Comment

ID: 42252931
Thanks for the confirmation Scott.  Just wanted to make sure.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question