Make copy of tables every week to compare changes (1 Viewer)

[gfultz]

I am trying to figure out a way to make a copy of some of my tables once a week so that I can run a report that idenitfies what has changed since the prior week. I figure I will create the tables and then create code to update existing data and append new records. I would also like this to be run at a set time every week.

Any ideas on setting this up? Writing the SQL should be easy, but the vba I am a little concerned about.

Thanks!

 

[WayneRyan]

g,

It should be pretty to use your backup copies of the database to accomplish
this. I posted a very rough little utility that would let you examine two
databases and compare tables. The date differences could be weeks, months
or years ... whatever.

I think it is "MDB Table Compare" and is on about the 5th page or so of the
sample database section here.

You basically need just a core set of queries to detect the Inserts, Deletes
and updates.

hth,
Wayne

 

[gfultz]

Thanks Wayne. I will check that out.

I am really hoping to be able to run these change reports easily as it is critical to identify things that are moving.

 

[gfultz]

Hi Wayne,

I see what you did and while this is a good utility for me, having a manager analyze data like that would be a chore.

I think ultimately the best solution for my issue is writing the code to update the table and append new records to the table. So, I would have a table MREQ and MREQwk for a weekly snapshot (amongst quite a few others). The only snafu I see is how to automate the process so Sunday at midnight this happens. I really don't want to make this a manual process. Any ideas on this piece?

Garrett

 

[vbaInet]

This could be quite a chore, just thinking of it. Because not only are you looking for new insertions or deletes, you also need to check for updates as well.

One way (might) be to create an extra column that will set the value to "Yes" if it was changed after that date. That could be a record check. For deletes and inserts, maybe create a seperate table that will save the name of the table plus the index where a record was inserted or deleted. This would all depend on the amount of data and the number of tables. Just a thought.

 

[gfultz]

A colleague that does a similar thing in sql server just suggested that i use a simple select insert to copy the tables at a specific time, adding an extra field that labels the week ending date of the data (The current date - 2 days). I could then run more historical data and purge after a month, 6 months, a year, as much or as little as I'd like.

That would also make the comparison query easy as I'd be looking for the most recent snapshot.

We also discussed writing a small vb app or script that would open the access db in a shell and run the code to perform this. Obviously I could then set this to be run automatically at a set date and time.

 

[vbaInet]

That was similar to what I had in mind, but with your friend's idea it would be more efficient to find new records. But how does it work for deleted or updated records?

 

[gfultz]

Yeah it would because I am selecting all records from the table and stamping the date the snapshot was taken.

Here is my sql code:

INSERT INTO zMREQ ( MRID, MRReqS, MRReqF, MRReqA, MRtoCR, WEdt )
SELECT MREQ.MRID, MREQ.MRReqS, MREQ.MRReqF, MREQ.MRReqA, MREQ.MRtoCR, Date() AS WEdt
FROM MREQ

Obviously that is one table only and using the current date function requires the code be ran at a consistent time.

Also, another set of code would be to delete all records with a WEdt earlier than 6 months ago. So essentially I am storing all of the weekly snapshots by WEdt (Week ending date) and I am keeping 6 months of history so I could technically run queries on the history to compile of the WEdt's of the same month to allow for month by month comparisons.

Right now I am planning to run this as a separate Access app that will execute a macro on open to run the vba code, provide the results of the copy, and then close out access. Eventually I will write this app in VBA and just call this in the shell. At some point this will be converted to SQL anyways, so I don't know if I will ever get to that point because with SQL, I can just set the queries up as a job to run atuomatically.

 

[gfultz]

Oh, here is the vba version of the sql code:

Function Snapshot()
On Error GoTo Snapshot_Err
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO zMREQ ( MRID, MRReqS, MRReqF, MRReqA, MRtoCR, WEdt )  SELECT MREQ.MRID, MREQ.MRReqS, MREQ.MRReqF, MREQ.MRReqA, MREQ.MRtoCR, Date() AS WEdt  FROM MREQ;", 0

Snapshot_Exit:
    Exit Function
Snapshot_Err:
    MsgBox Error$
    Resume Snapshot_Exit
End Function

 

[vbaInet]

Have you ever used Replication before? Do a search to see if that's what you're after.

By the way, in your code, you need to set back the warnings back to on after you've inserted into your table.

 

[dfenton]

How, exactly, would replication help with the stated problem? Seems to me it would cause you to lose the record of what has been changed so you couldn't create a report.

It's not clear to me what the original poster is trying to accomplish. The answers to the original question have assumed that he's synching two databases, but I don't think that assumption is warranted.

The code to do that is not really that difficult. You just write code to loop through the TableDefs of one database and then write SQL to do the inserts/deletes from the other database (using outer joins), and for the updates, you can write code that walks the Fields collection of each TableDef and writes UPDATE SQL to update column-by-column. I've written this code dozens of time (just did it again last week for an import process on a new client's existing app).

But none of that is in line with the original question, so I'm not sure what is really being sought by the original poster.

 

[The_Doc_Man]

I'm with dfenton on this.

You could use a Windows Scheduler Task to copy a file at a given time and then trigger a command-line activation of Access. Look up the command line options, in particular -X as an option. Don't forget that the macro you need to do a RunCode action needs to end with a Quit action.

I'd recommend that you actually do this comparison in a third database that opens the other two databases using the FileSystem object to identify the files and get their dates. The date of database file creation would tell you the cutoff date for changes in the two databases.

With a little bit of VBA code, you can open more than one database in the workspace. Then you have to start worrying about qualifying them so that you know which collection you are stomping through when you stomp. Which means lots of prefixed variables.

It would be easily possible to generate a couple of loops to step through the Tabledefs collection to identify a table. Then for each such table, visiting that table in PK order, step through the Tabledefs(m).Fields for each record to do a comparison.

You'll find three cases:

1. Same record keys but something is different in a non-PK field. So you just compare them and call out the differences.

2. A record in the old table doesn't exist in the new (current) table - meaning a deletion has occurred.

3. A record in the new table doesn't exist in the old table - meaning an insertion has occurred.

To make this reliable and keep things apples to apples, you need to have a PK, even if synthetic, for every table.

There is also the odd chance that you can find a table in one of the copies that isn't in the other copy, also a loggable event but of a different type.

This is a perfect situation to implement nested For Each... Next loops in a structure like...

For Each tNewTable in newdb.Tabledefs

{look for the corresponding table in the olddb.Tabledefs collection}

{open a table-type recordset to the current new table}

Code up a loop for each record in the new recordset. If you find a case where stepping forward with the old and new recordsets brings up different PKs, you have found a new record or a deleted record, depending on which recordset has the higher number. If the new recordset is lower, you probably have meaningful keys AND a record was inserted. If the old recordset is lower, you have identified a deleted record (that isn't in the new recordset). If they have the same PKs, then for each field in the Fields collection, compare the fields to identify the differences. At the end of the collection, if you have found no differences, then those records are the same.

At the end of each table you will have found zero or more changes, new records, or deletions. Step to the next new table.

At the end of the stepping through the new tabledefs, step through the old one only to verify that no table was deleted.


Just some random thoughts about how I might approach the problem. Look it over, ask questions. Don't presume that I am 100% right. These days I'm feeling very normal in the "making mistakes" department.

 

[vbaInet]

I forgot the poster wants to run a report for the changes. So yes you're right David, Replication shouldn't be an option.

I think the poster should be thinking more of a log file than a report. Write changes to the file: TableName, TableID, FieldName, Date-Time-Stamp, Appended_Or_Deleted_Or_Insterted. Loop through the entire file and act accordingly. Copy the contents of the file to a temporary Table, sort the Date/Time stamp in descending order so you start checking from the most recent update. Just an idea.

 

[Peter Bellamy]

If the existing tables had an extra indexed column, 'DateStamp' added.
A date stamping function written and used by all the front ends that can mod the tables.
Then a query would find all the records that have changed since the last time.

Is that not practical or have I missed the point??

cheers

 

[dfenton]

You could use a Windows Scheduler Task to copy a file at a given time and then trigger a command-line activation of Access. Look up the command line options, in particular -X as an option.

If all you're doing is running SQL, I'd suggest doing the whole thing in vbScript using Jet, and save the overhead (and problems) of launching Access (which will work reliably only if the user account it is runing under is logged on).