Track Changes in Access Databases

[VinnyPs]

HI,

I have started working on an existing (very old) project including FoxPro and Access dbs. There are many dbs (64 to be exact). Is there a way that I could track changes in all dbs? So when I change something I can see what and where the data has been written.

thanks,
Vince

 

[Trevil]

Do you mean 'track data changes' or 'track database design changes'?

For design changes, we use SourceSafe and it will show all changes that have been made. This also allows multiple developers to be changing the database at the same time.
If you don't already have a license for it, you may not be interested.

If it's data changes, then I may have some code laying around that will compare all data between two database. You could change that code to use multiple databases.

"Hmmm, it worked when I tested it....

 

[VinnyPs]

It's for data changes. Any code/ideas would be very appriciated.

Thanks

 

[TheRealDeal]

You could use a 2 table scenario like Trevil mentioned.

One is the original, the other is the copy.

Name the copy tables "copy*" <-- (*-the name of the original table) -or- however you would delineate.
ie Customer and copyCustomer

Open the original(rst1) and copy*(rst2)

Drive the comparison from the rst1, record-by-record - seeking in rst2. Once found, use a field declaration to compare the records field-by-field; otherwise, add the new record. If you notice a difference, kickout an update to rst2 and record your table/record/field differences to your master table that contains the changes to all tables. This master table could contain such things as the table, the record, and field(s) that changed and/or even when added.

If you need more help setting up the declarations and possibly some iteration structure; it would not be a problem.

Again, if the 2 table approach is your choice.

 

[VinnyPs]

thanks , I've started coding what you've told me creating a Table including the differences found.
The concern I have is the time it will take to compare, because I have about 70 tables and some contain more then 200000 items.
Do you think it makes sens comparing field by field in my situation?

 

[TheRealDeal]

I would tend to say no, it would not make practical sense given your situation. Do you have some field within the tables that tells the last-update of the record or is it that the problem? Nonetheless, you could try it and see what kind of results you get. Might be within an acceptable timeframe.

 

[VinnyPs]

There is no field to indicate last-update, also some dbs are in FoxPro 2.6 and I can't convert them to Access because there is presently a system functioning on these FoxPro dbs. So I can't add any fields to the existing dbs, I can only link to them.

 

[VinnyPs]

Would there be a way to supervise what is written in any of the linked tables, and write this (in parallel) in a seperate table that would be my Tracking_Changes_Table (with the same fields: table/record/field/when)?

 

[Trevil]

Can I get a little clarification...
You have 64 databases (not tables); some in Access, some in FoxPro.
Does each database perform the updates on it's own data, or is there some degree of centralization where you may have an Access database that has links to x number of tables in other databases and that Access database is used to perform the updates?
How many different tables are we talking about?
Is the table structure the same for any databases?

To keep this simple, lets say you have DB1 and DB1 has 20 tables. If you want to track changes to any or all of those tables, you would need some table structure to use as a 'before image' / 'after image' on either a field level or at a record level. The easiest approach is to clone the structure, then develop code to capture changes as they occur (plus some method to report changes).

You could develop a standard routine (I could probably find the one I'm thinking about) that could be used for capturing the changes to any given table, and that should work for most, or possibly all of your tables.

I'm curious as to the scope of this problem. You need to decide if this is an 'Auditor requirement' or if some whacko in your organization thought this 'may be a fun thing to do'.

"Hmmm, it worked when I tested it....

 

[VinnyPs]

There is 30 FoxPro dbs and an Access db with 34 tables.
The use of this, is that we need to add some functions to the existing program. We do not have access to the code of the existing program, and we have started programming around it, in Access. To understand it better, a "change tracking function" like this would be very usefull. Does it make sens?

Thanks a lot,
Vince

 

[Trevil]

Take a look at FAQ191-291 (

http://www.tek-tips.com/faqs.cfm?fid=291)

to build transaction logs.


"Hmmm, it worked when I tested it....