Saving Historical Copies of DB (1 Viewer)

[SteveClarkson]

Hiya,

I realise this could well go against almost every DB rule in the book, but figured I would ask it anyway!

I have a database, which pulls all it's data from other databases - some in SQL, some in Oracle, and some from other Access DBs.

It then combines it all, performs dozens of queries on it, and allows me to produce necessary reports on it - all fine.

I have been asked to make it save historical copies of all the data it uses. The reason for this is the Financial Services Authority, who insist that the checks we are doing on this data is all stored, so that if an auditor arrives tomorrow, and asks me to prove the data from 3 months ago was processed correctly, I have to be able to come up with that 3 month old data.

I thought the easiest thing to do would be to use a series of make-table queries to move all the tables data to an external database, which can then be archived.

Does anyone have a way of allowing me to save the entire database, as at NOW - to another database?
I would need to make all the tables LOCAL, rather than linked?

Thanks! (and sorry for the unnecessarily long post!)

 

[The_Doc_Man]

Yeah, ain't fiduciary responsibility a booger?

To do this, several paths come to mind. I'm going to take "the easy way" out.

1. Prepare your primary DB
a. Be sure that EVERY RECORD has a timestamp field in it.
b. Have a table that records the timestamp at which you do your processing. Don't insert a record until all processing is done.
c. Have a yes/no field in every record; call it "RdyToCopy" or something similar to that.

2. Build another database that holds the same tables you have now
a. complete with timestamp fields.
b. complete with the RdyToCopy field.
c. containing EVERY table you need to repeat your computation.
d. Link to the tables externally. The names don't have to match as long as you know what the names really are.

3. When you are done with the computation, in your main table,
a. update your date of last processing table.
b. update the RdyToCopy flag based on the timestamp on the records being older than the timestamp of last processing.
c. Do an append query of all "RdyToCopy" records to the linked tables in the older database.
d. Now do an erase query of all "RdyToCopy" records in the main database.

NOTE: For records that you cannot delete because they span multiple processing periods, the "RdyToCopy" flag should be set by default in any new records for those tables, but after you copy the ready records from the permanent tables, update the flags to NO so you won't copy them a second time.

Now ... to do this REALLY crazy - but possibly manageable,... before the first time you use the database defined in step 2, make a copy of that file with good structure but no data. Rename it to something like "Prototype" and keep it hand.

Now, once per year, decade, whatever... manually copy the backup database to a file with a year in the file's name. Then copy the prototype database (which is blank) to become the current version of that historical database. When you do this, you will have to manually reestablish the links.

OR... manually copy the backup database to another year-tagged name and then erase the tables inside (thus avoiding the need to recreate the links). Although if you compact the external database, I think you might need to remake the links anyway.

Sounds complex? Could be - but here is how you make it less painful. Build the append queries that you need and the delete queries you need. Then create 1 macro that does all the copying and deleting you need in the storage / archive database - as a single task. This should make it very easy. All that is left is the yearly manual archiving step.

Hope I didn't lose you in the midst of all of that.

 

[SteveClarkson]

Lose me? Several times!

Thank you very much indeed for your reply. I will have a bash at that later today, and post on how well I managed it.

Once again - thank you! Flamin' FSA - they do like making life difficult!!!

 

[SteveClarkson]

Doc_Man - have had a go - and it seems to work well. Getting timestamps in everywhere was a bit of a pain - but we got there in the end.

Thank you very much for your very in-depth and helpful post!!!