Solved Determining last update date of all objects (1 Viewer)

[Pat Hartman]

In the Navigation Pain (sic), if you change view by to Details, you can see the date created and the date modified. I want to find this date so I can use it to automatically control my backup procedure rather than having it prompt me.

I've tried the MSysObjects table since that is the easiest method but the date modified seems to be connected somehow to DAO (probably because Access objects live in a Jet/ACE database) So, if what you updated didn't have any DAO involved, then the date in MSysObjects doesn't get updated as you would expect.

Since MS tells us to not rely on the MSys objects anyway, I created a VBA procedure that reads the containers and puts them into a table. This method is not desirable since it causes bloat so if I were going to use it, I would probably create an array and sort the array but I didn't get that far since the collections seem to have the same flaw as the MSysObjects table.

So, the question becomes, how do I get to the dates shown by the Nav Pane?

I created a sample for you to examine so you can see the problem more easily. The database has only a few objects. The problem is persistent for unbound objects like frmRun. Follow the directions on the opening form to see it. But the error occurs in other situations that are transient and so hard to document.

 

[MarkK]

Pat, I am pretty sure those dates are properties of the AccessObject, so to get that data about a form, you reference a member of the CurrentProject.AllForms collection, like...

? Currentproject.AllForms("frmRun").DateModified
7/28/2022 3:40:12 PM

 

[Pat Hartman]

Thanks Mark,

This does not give me the warm fuzzies. I modified the code to capture

CurrentProject.AllModules(doc.Name).DateModified
CurrentProject.AllReports(doc.Name).DateModified
CurrentProject.AllForms(doc.Name).DateModified
CurrentProject.AllMacros(doc.Name).DateModified

in addition to doc.LastUpdated which comes from documents in a container. So, you can now see the three values in the query. The "All" value so far seems to match what the Nav Pain (sic) says so I'm taking that as the definitive opinion.

Apparently the Access developers don't understand normalization since they seem to keep the same piece of data in multiple places and don't update them consistently. There's a couple of websites I use regularly with the same problem like my TD AmeriTrade page and the site I use to play bridge on.

Life would be oh so much easier if the data in MSysObjects was correct. Having to loop through several separate collections means I need temp storage which is going to lead to bloat since I don't really want a separate temp BE for this. I'll go back to my hole and think about it for a while. Maybe I can load arrays and then sort them. I don't generally do a lot of array work since tables are natural arrays so I don't know if they cause bloat. I'll guess I'll find out

 

[MarkK]

Pat, this interests me. Some time ago I wanted to update my FE version number automatically by just reading data from MSysObjects--or somewhere--and have the latest FE version be equal to the latest date that objects in some finite object set--not data--got modified. Well, I'll be @#$%^&*ed if I could find a predictable or consistent place where LastUpdated produced a meaningful date/time that I could use for this purpose.

Anyway, I have not looked closely at your Db yet, but I will, and I'll post back. Maybe between us, or if others see value here, we can find something that yields a LastUpdated date/time we can use for various purposes. More to come,

Mark

 

[isladogs]

I looked into both this and a similar issue several years ago.
I wanted to use code to retrieve the description that can be added to objects in the nav pane and then display this in a list of database objects as part of my utility app

View Database Objects & Metadata

This article includes an example app used to view all objects in a database together with their properties and metadata.

www.isladogs.co.uk

Anyway, at the time I failed to get that info. I'll have another look at it

 

[isladogs]

UPDATE:
I can now get the object description from the nav pane programmatically.

For example:

CurrentDb.QueryDefs(doc.Name).Properties.Append CurrentDb.CreateProperty("Description", dbText, True)  
rs!ObjDescription = CurrentDb.QueryDefs(doc.Name).Properties("Description")

So far only tested for tables & queries. Will try other objects later today

 

[Pat Hartman]

@MarkK Since I know the MSysObjects is unreliable, I update my MSysObjects query to force an "auto" update when I know I want one and I think I haven't changed anything that gets properly logged but it would be so much easier if MSysObjects was reliable. Now that I have another option, I'll see what I can do with it. It never occurred to me to try the "all" collections. Why would I assume them to be different from the collections container

My autobackup relies on a table where I log each backup. The code compares the last backup date with the latest updatedate from MSysObjects to determine if a backup should happen. At the moment it is hardcoded to look for my login since there is no point backing up the FE for users. That could be changed to a table to make it more usable.

It also does two types of backups. Full file and exporting all objects except tables to text. This has saved my bacon on more than one occasion.

@isladogs that could be useful.

 

[isladogs]

@Pat Hartman
I've fixed the issues that prevented you getting the DateModified property for tables / queries & macros

The problems were all naming:
1. For tables & queries the property is called LastUpdated (not DateModified)
2. For macros, although the container is called Scripts, you need to reference the AllMacros collection

As you can see, I can now get the Description for tables & queries but not yet for other objects.
I'll have another go at that later if I get time

P.S. I find it annoying that all objects can be saved as text EXCEPT for tables

 

[Pat Hartman]

Thanks Colin but I already posted the version I changed to pick up the date from the "all" collections for forms/reports/modules/macros. I did it using the curmudgeon method because this was a sample after all.

Now that I think the "all" solution works, I will make a stab at rewriting using arrays so I don't have to permanently save the data into a table. Sticking with the table method means delete/add every time I need to check for a change date > the last backup. Or, maybe I don't need to create arrays after all. Just save the date and associated name and overwrite anytime I come across a newer date. I really did like using the MSysObjects because I could also show the last changed object on my switchboard and it always stayed current. Given the awkwardness of having to read through a bunch of collections, I won't waste the time do that every time the switchboard regains the focus.

 

[isladogs]

I had downloaded the modified version in post #3.
These were the results:

It didn't work for tables/queries & macros for the reasons I stated above.

 

[Pat Hartman]

Sorry, I should have posted the picture of the modified query so you wouldn't have corrected the old version. But a big thank you anyway

 

[MarkK]

Here's a stab at this. This approach loads all the AccessObjects in the database into an array, sorts the array, and thereby finds the Min and Max DateModified values. This might be a reliable way to find the latest date/time that any database object was updated.

 

[isladogs]

@MarkK

Here's a stab at this.

That looks pretty darn impressive for a 'quick attempt' at this task!
It certainly seems to work - now I just need to understand the code

How easy do you think it would be to adapt it to get both the description and date created items as shown in the navigation pane?

 

[Pat Hartman]

Thanks @MarkK but it doesn't work for me there is a missing reference to an addin.

 

[isladogs]

Forgot to mention that - just remove the reference & it works

 

[MarkK]

Yeah, that mscorlib.ArrayList is pretty cool because you can sort member objects by the value of their properties--or whatever you want--by writing a class that implements mscorlib.IComparer and provides the logic for the sort.

Apart from that it's pretty simple, it just adds all the AccessObjects in the database into a collection, and sorts them.

How easy do you think it would be to adapt it to get both the description and date created items as shown in the navigation pane?

This is strangely hard, because the Description field--available via the right-click "View Properties" popup menu item from the nav pane--does not exist in the AccessObject. The DateModified and DateCreated are there, and appear to correctly correspond to the displayed data in the nav pane. So getting DateCreated is easy, getting Description is, well, I don't know. I assume hard.

 

[MarkK]

Thanks @MarkK but it doesn't work for me there is a missing reference to an addin.

Yeah, sorry Pat, just clear that reference.

 

[Gasman]

Just FYI, does not appear to work in 2007. Crashes Access and just restarts it.
I hope I am using it correctly. I tried Run with no dates and Run with dates in just that DB.
Removed the missing reference as well.

Edit: After subsequent attempts (3) it is now working?

Very nice BTW

 

[CJ_London]

Only problem I'm seeing is if you save a change in any module (including form and presumably report modules (not tested)) then all modules update to the same last modified date. Think it has already mentioned that it's the whole VBA estate that get updated but does mean a modification to form/report code will affect standard and class modules.

One of the tabledef properties is recordcount - this would appear to not update when records are added as it doesn't update the date modified value.

Updating the date modified value seems to be triggered by physically saving the table or query by clicking the save button- whether or not a change has been made

Not tested yet but a change to a table by using a DDE might also not update the date modified value

Also need to test to see if vba code to update a querydef sql property updates the date modified value

 

[isladogs]

I made use of the last update date property as part of my recent web article:

How Access Stores Queries 2

The second part of an extended article explaining how Access uses the MSysQueries system table in conjunction with other system tables to display the query structure in the query design window and determine the last saved query view.

www.isladogs.co.uk

I used this in conjunction with obtaining the last saved view for each query:

The reason for doing this is explained at the end of that article