Tracking changes to database - queries/tables/etc (not data)

[aytee111]

Does anyone know how to be able to track when a database object is changed? I'm thinking something along the lines of making a copy of the database when it is opened then do a comparison afterwards. Seems like a convoluted way and Access doesn't carry all information - it carries nothing to do with Macros, for instance, how can I see if a macro has changed?



Any help/ideas/suggestions will be most welcome.

[June7]

I suspect this would be difficult to impossible.

Bing: Access track design changes

http://bytes.com/topic/access/answer...design-changes

[aytee111]

Pity! Someone should write something for us!

Thanks anyway.

[ranman256]

It's a Heisenberg effect.

[ItsMe]

You could possibly use DAO to go after objects like tables and queries using TableDefs and QueryDefs
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

You could collect data from the various properties and write the results to a table.

Here is an example of enumerating field properties and writing the field properties of a given table name to a table. So you would do something like this only for the table defs and not the fields.
https://www.accessforums.net/queries...tml#post205017


To get other objects like Forms, Reports, Standard Modules, and Macros you could do something like this

Dim objMacro As AccessObject
For Each objMacro In CurrentProject.AllMacros
Debug.Print objMacro.DateModified
Next

[aytee111]

Thanks a lot! I will try that.

[June7]

So open db and execute code that saves various property settings into a table? What do you want to do with this data?

Could also have code that identifies user when they open database and automatically enters username and date/time info into table.

[hapm]

I currently working on something like that for work. It doesn't track every single change so. It exports all objects as text to a git repository to get it under version control. Changes can be looked up by making diffs over the different versions. Not sure if this matches your use case. If so, let me know and I will ask my employer if I can distribute it under an open source lisence.

[aytee111]

All these brilliant ideas! First I tried making copies of the tables and queries into another database on open, but it was taking way too long. Then I copied over just the structure of the tables (not interested in data) which quickened the process but is still taking rather long. On close I will have to check for differences, again this is going to take too long. Maybe hapm's idea of copying them as text files with version numbering might be better/quicker - hapm?

Not yet "solved" - still in experimentation stage. Keep those ideas coming!

[ItsMe]

Perhaps you can use a combination of things. For instance, in order to inventory objects and their modified date, you could do this via automation. A second DB could enumerate the production DB during after hours. The second DB could create an instance of the production DB.

[aytee111]

By "create an instance", do you mean file copy or is there a better way?

[ItsMe]

By "create an instance", do you mean file copy or is there a better way?

I mean instantiate an Object in the Second DB like this to access the members of the Original, production (live), DB.

Dim OtherDB As Object
Set OtherDB = GetObject(strPath)
OtherDB.Application.SomeMember

I have not tried to do too much as far as methods and such but should be able to do just about anything, I would imagine.

[hapm]

There is a problem with the "export as text" approach for tables. I use the hidden sub Application.SaveToText to do the export, and this sub doesn't take tables. I wrote my own method for tables, but it isn't very fast as it iterates over every collection in the TableDef object and filters unneeded properties. The resulting file looks somewhat simmilar to the other files generated by Application.SaveToText, but as it is self written Application.LoadFromText doesn't take the file. If you want to import the table again, there would first be the need to write a seperate import function for it.

To avoid exporting objects that didn't change, you can use the DateModified property of the AccessObject instance and compare it to a last exported date saved somewhere else. I use a custom property on the AccessObject that saves the last export date for this. It reduces the number of objects to export and therefor the time needed for the whole process.

[aytee111]

So far I copy all objects to another database on both open and close (tables, queries and macros). For tables I use TransferDatabase/acExport which allows me to copy the structure only, saving quite a lot of time. For queries and macros I use CopyObject. It is easy enough then to do all the comparison logic in the logging database instead of in the production db.

I don't know if it will be more efficient to check the DateModified/Created on the close of the prod db prior to making a copy of the object. This will entail extra work in getting hold of each object's properties and then checking to see if it had changed. I haven't tested this to see if would be quicker.

One side effect, when checking the queries to see what exactly had changed, I am unable to access any of the fields or properties due to the fact that the queries won't run in the logging db - the source tables are missing. All I can do is compare the SQL. I break it up into subsections based on SELECT, FROM, etc to try to help the administrator, so that it isn't such a daunting task for them.

It does take time to do all this while opening and closing the prod db. I have tried to get the developers to keep their databases clean by getting rid of all the unused objects - the ones called "_old" and "x..." and "test", the tables that are no longer used, etc. Not an easy task!

[NTC]

I'll be the buzz-kill guy in saying I don't see the need in context of the definition of what Access is and it's goal in being a departmental/small business solution..... so I can't consider it a flaw or criticism that this feature is not part of the core product.

The product offers the accde format to lock the code so it can't be altered. The idea of allowing it to be altered but yet having a need to track every design change is very high level of version control that is just not appropriate for the product. IMHO....