asp.net consultancy chorley ASP.NET Server Side Scripting Wigan
Website Development Radcliffe

IT Services And Support

Email : ren@techsolus.co.uk
Mobile Phone : 0788 68 41 411
Answerphone : 01204 469683
bespoke invoice systems Standish Information Technology Advice Worsley
    development systems Ramsbottom
data manipulation Farnworth access databases Whitefield SQL connections Atherton
online accessible software Leyland software compatible Coppull


Get connected and Online Tottington
remote management Makerfield code and design Aspull

Merge-Restore Deleted Data From Backup - Access - 28/04/2016

ARGH! You have been good and kept a backup but then deleted a load of stuff you should not have from the live Access database. You don't want to replace the live one with the backup as more stuff has been altered since then. You need to merge back in the deleted data without screwing up the new data.

How to get the data back in

So we have LIVE.mdb and BACKUP.mdb. In both of these we have table TABLE. We want the lost data in TABLE - BACKUP.mdb to fall neatly back into TABLE - LIVE.mdb

Open BACKUP.mdb and rename TABLE to OLDTABLE

Export OLDTABLE into LIVE.mdb

Open LIVE.mdb now you will have TABLE and OLDTABLE. Open the create a query thingumyjig

under SQL enter - 

INSERT INTO TABLE
SELECT * FROM OLDTABLE WHERE OLDTABLE.UNIQUE_ID NOT IN(SELECT TABLE.UNIQUE_ID FROM TABLE)
 
SO!!! Slow down and check and learn
 
At the end it reads (SELECT TABLE.UNIQUE_ID FROM TABLE) So this will select ALL the unique_ids from the live, current, working table.
 
We DO NOT want these, these records still exist and they may have been updated. We want to leave these alone. So we...select everything from the OLDTABLE whos unique_id is NOT in the working live table. I'd start by checking a few records by running the SELECT statement first of all.
 
Then we do the INSERT bit.
 
This is the simple explantion. If you're going to use this you need to alter everything accordingly. Backup. Backup again. Put a backup on a USB stick and put it in a safe. Backup at each stage. Work through things slowly. 

Post A Comment

Name Comment
Ian Soady Just a quick comment. The NOT In syntax can end up with being very slow if it's a huge database. Another way to do it is the left join: SELECT * from [Cash expenditure] left join [backup cash expenditure] on [Cash expenditure].key = [backup cash expenditure].key where [backup cash expenditure].key is null; is an example from my own accounts database.....
programming services asp.net specialist
Valid XHTML 1.0 Transitional
Admin
GD