Just as an observation, and I don't know why, but the last modified data of all modules always shows as the data the database was last opened? Do any of the methods in here produce the actual modified date, or is that not available. It's a pain at times to not be able to review the recent changes.
For modules, you won't get the last modified date. Apparently 'LastUpdate' refers only to the module documents collect as a whole.
I found that I needed to run a hash comparison (MD5 Hex as I recall) to see if any changes have occurred and then use the date of the comparison as the last modified date.
I suppose you could get round it by copying any module you ever edit, and then delete the original. Then the date created would also be the date modified., but it would be a pain to have to do that all the while.
I suppose you could get round it by copying any module you ever edit, and then delete the original. Then the date created would also be the date modified., but it would be a pain to have to do that all the while.
I do the same thing because it helps to see the changes over time. But the thread is about figuring out if anything changed since the last backup and MS doesn't make it easy.
OK - Here's a working solution.
I created a table with these fields, called TblDBSObjects
ContainerName - text The first two fields comprise the PK
DocName - text
DateCreated date
DateUpdated date
DateUpdated1 date
DateUpdated2 date
DateUpdated3 date
DateUpdated4 date
DateUpdated5 date
This module checks the above objects table for every object in the database to see if it has already been added to the table, so the first run adds all objects. If the object is already stored, the process compares the lastupdated date actually held on the database object with the DateUpdated date stored in my data objects table (Note that I am testing the value returned from the DAO object, which I presume is the same as you get using the mSysObjects table.)
If the date has changed, I shuffle across the updateddates in my table, so I get a record of the last change, and the five previous changes. I couldn't easily see how to compare dates to the second. (Note that I am using UK date formats.)
My test
If doc.LastUpdated <> lastdate failed to work correctly
I had to use this
If Abs(DateDiff("s", doc.LastUpdated, lastdate)) > 0 Then
I can only think that trying to compare the numeric values of the dates fails somehow because of the precision, but I don't understand why.
The second test picks up changes only.
Despite all of this, I just cannot find a way to read the lastupdated date of a module that is shown in the navigation pane. It's just not the date stored in the document record of the modules in the modules container. MS Access must retrieve the date from somewhere else for all modules. The .lastupdated date in the module document is actually the same as the .datecreated
I would note as mentioned above that I never use MSysObjects directly for anything. I find I can get any information I need from the DAO object model. Maybe the DAO object model is another way of reading the same table?
This exercise also finds numerous objects beginning with mSys, and deleted objects beginning with ~ which aren't normally shown by default in the navigation pane. I don't know whether it detects the deep-hidden objects that are mentioned from time to time. but I wouldn't be surprised if it did. I can't see where else they could lurk, if you will.
One other note. Note the the container for the tables includes both tables and queries. You can have the same object name in both tables and queries. Offhand I think there is a flag or a property on the document to distinguish between a table and a query - but I didn't check that.
Here's the subroutine
Code:
Sub populateObjectsTable()
Dim db As Database
Dim ctr As Container
Dim doc As Document
Dim lastdate As Date 'used as a dloolup result to read/save the .lastupdated value stored in the tblDSBObjects
Dim s As String
Dim changes(3) As Long
changes(1) = 0
changes(2) = 0
changes(3) = 0
Set db = CurrentDb
For Each ctr In db.Containers
For Each doc In ctr.Documents
On Error GoTo noobject
lastdate = DLookup("dateupdated", "tblDBSobjects", "containername = " & Chr(34) & ctr.Name & Chr(34) & _
" and docname = " & Chr(34) & doc.Name & Chr(34))
If Abs(DateDiff("s", doc.LastUpdated, lastdate)) > 0 Then
GoTo updateobject
Else
changes(3) = changes(3) + 1
End If
nextdoc:
Next
Next
MsgBox "Completed" & vbCrLf & _
changes(1) & " new objects inserted" & vbCrLf & _
changes(2) & " objects updated " & vbCrLf & _
changes(3) & " objects not changed" & vbCrLf & _
""
Exit Sub
updateobject:
s = "UPDATE TblDBSObjects set "
s = s & " dateUpdated5 = dateupdated4 "
s = s & ", dateUpdated4 = dateupdated3 "
s = s & ", dateUpdated3 = dateupdated2 "
s = s & ", dateUpdated2 = dateupdated1 "
s = s & ", dateUpdated1 = dateupdated "
s = s & ", dateUpdated = #" & Format(doc.LastUpdated, "dd mmm yyyy hh:nn:ss") & "#"
s = s & " where containername = " & Chr(34) & ctr.Name & Chr(34) & _
" and docname = " & Chr(34) & doc.Name & Chr(34)
On Error GoTo failupdate
CurrentDb.Execute s, dbFailOnError
changes(2) = changes(2) + 1
GoTo nextdoc
failupdate:
MsgBox "Error updating existing object record" & vbCrLf & _
"update string was " & s & vbCrLf & vbCrLf & _
"Error: " & Err & " Desc: " & Err.Description
MsgBox "Aborting"
Exit Sub
noobject:
Resume newobject
newobject:
s = "INSERT INTO TblDBSObjects ( ContainerName, DocName, DateCreated, DateUpdated ) "
s = s & "SELECT " & Chr(34) & ctr.Name & Chr(34)
s = s & ", " & Chr(34) & doc.Name & Chr(34)
s = s & ", " & "#" & Format(doc.DateCreated, "dd mmm yyyy hh:nn:ss") & "#"
s = s & ", " & "#" & Format(doc.LastUpdated, "dd mmm yyyy hh:nn:ss") & "#"
On Error GoTo failinsert
CurrentDb.Execute s, dbFailOnError
changes(1) = changes(1) + 1
GoTo nextdoc
failinsert:
MsgBox "Error saving new object record" & vbCrLf & _
"insert string was " & s & vbCrLf & vbCrLf & _
"Error: " & Err & " Desc: " & Err.Description
MsgBox "Aborting"
Exit Sub
End Sub
Hi Dave
Thanks for making the effort to do this.
I'm assuming you created this in A2003 or similar as in newer versions of Access it fails with error 3127 on the very first item MSysDb in container Databases
By adding the line If Err = 3127 Then Resume Next to the error handler, I was able to run it and it found all standard & system tables together with all other database objects including relationships and recently deleted objects beginning ~TMPCLP.
NOTE:
1. Due to the fact that tables and queries are all found in the Tables container, all queries are listed as Tables which could be confusing
2. Deep hidden tables (system or user) are NOT picked up by this method as they are invisible to the Tables container
I altered a table, imported a few other objects and ran it again & the records for the table tblDBSObjects & the altered table were modified:
Whilst I could quibble about the non-normalised structure of your table, it seems to work as intended (once error 3127 is trapped).
I can see an update history might have merits, but apart from that I'm not sure I see any need to store the data in a table.
It seems to me that the approach is basically the same as that in post #8 except for saving the results
As you say, it still gives identical values for date updated for all modules as that is what is stored in the properties for each.
There is no work round that can overcome that point
For info, I use a similar approach to list and then recover deleted objects:
This is a detailed guide to the different methods of recovering different types of database objects, together with the advantages and disadvantages of each method
Interesting Dave. Thanks. Did you notice the earlier solutions? This is the third. Also, you can't ignore the "~" objects if they are queries because Access prefixes all the temp querydefs it makes whenever we use an SQL string instead of the name of a querydef or table as the RecordSource or RowSource in forms and reports.
Early versions of Access created new querydefs for each SQL string whenever the object was opened. At that time, they really were temporary and they were not reused, hence leading to lots of bloat. I'm not sure when, but some time the "temp" querydefs became permanent although the leading "~" was retained because Access created the objects and it would have been too confusing to have them be visible.
I also noticed that the containers solution still doesn't show the correct dates for certain objects so it's back to the drawing board unless someone from MS can be interested in giving us a clue how to find the actual, top secret, eyes only, last update date
Version 4!
Just for info, I now have code to get the Description of each object where this has been added in in the nav pane.
In the end that was ridiculously easy to obtain
Currently being saved to a table but I'm hoping to avoid doing that
I'm also using code based on Containers but to avoid confusion, I've done some renaming of the container name (listed as Type)
a) Scripts - changed to Macros
b) Tables - split into Tables & Queries
I'm working in the same db as the NavPaneHelper form so I've included the latest version of that in the screenshot
Its not yet finished - still trying to get code for the items highlighted
@isladogs
I just converted it to A2016 and tried again. I didn't get the error 3127. "DateUpdated" was a date field in my new table, and then 5 additional fields for the changes. I know it wasn't normalised - it was just to demonstrate, and track version changes. Maybe you didn't add the field called "DateUpdated", and just added the fields numbered 1 to 5.
@Pat Hartman I always thought the queries starting ~ were deleted/superseded objects, that remained until a C&R. I generally test for left(1) = "~" and left(4) = "msys" to ignore non user tables. I was surprised when you said not all containers were showing the lastupdated date - I thought it was just modules, but I checked in the A2016 version, and then in the A2003 version, and both forms and reports don't seem to show the lastupdated date correctly in either A2003 or A2016, although the navigation pane does show the correct updated date for those objects, unlike the modules - so I will have another look at that. I changed a couple of queries and just thought it was all working correctly.
I have now added code to test whether a document in the "tables" container is a Query or a table, by testing whether there is a querydef or not. There ought to be a better way, but I don't think there is.
I came across this thread about .lastupdated returning incorrect values. This thread references KB299554, but this link now gives a 404 Error. According to the thread, MS never fixed this.
Version 4!
Just for info, I now have code to get the Description of each object where this has been added in in the nav pane.
In the end that was ridiculously easy to obtain
Currently being saved to a table but I'm hoping to avoid doing that
I'm also using code based on Containers but to avoid confusion, I've done some renaming of the container name (listed as Type)
a) Scripts - changed to Macros
b) Tables - split into Tables & Queries
I'm working in the same db as the NavPaneHelper form so I've included the latest version of that in the screenshot
Its not yet finished - still trying to get code for the items highlighted
The table description is a property that gets created when first populated for each item. It's a bit of a pain. I think you mentioned that already.
Tables use the tabledef.attributes value, which is a long value covering a number of options (summed)
Hidden is attribute 1
System is attribute -2147483646
You should be able to "and" them, although I am not sure how you "and" a negative. Edit. Maybe that negative is the large "sign" bit of the attributes word. It looks like 2^31 is that number, but with an 8 on the end, rather than a 6, and of course the number is negative.
I presume other objects queries, forms, reports etc use the same settings in their own object model.
I've been trying to solve this problem for years and not had any success. I understand that MS doesn't want us to rely on the MSys tables for anything and I get it although that would be by far the simplest solution since a query solves my problem directly. But, even the code that gets the dates from the containers fails to match what the Nav Pain (sic) shows (which seems to be the correct value) so that leaves us with NO OPTION that I have been able to find.
Just FYI, tables and queries are always correct since they involve DAO but nothing that doesn't involve DAO is reliable. Sometimes the dates are correct and sometimes they're not but I can't put my finger on the variance.
Since I don't actually care which item got updated since the last backup for the purposes of my automated backup, I've taken to always updating a query when I KNOW I want a backup but having to do that defeats the automatic feature I'm trying to install. If I can remember to update a query to force the backup, I can remember to make the backup
@isladogs
I just converted it to A2016 and tried again. I didn't get the error 3127. "DateUpdated" was a date field in my new table, and then 5 additional fields for the changes. I know it wasn't normalised - it was just to demonstrate, and track version changes. Maybe you didn't add the field called "DateUpdated", and just added the fields numbered 1 to 5.
@Pat Hartman I always thought the queries starting ~ were deleted/superseded objects, that remained until a C&R. I generally test for left(1) = "~" and left(4) = "msys" to ignore non user tables. I was surprised when you said not all containers were showing the lastupdated date - I thought it was just modules, but I checked in the A2016 version, and then in the A2003 version, and both forms and reports don't seem to show the lastupdated date correctly in either A2003 or A2016, although the navigation pane does show the correct updated date for those objects, unlike the modules - so I will have another look at that. I changed a couple of queries and just thought it was all working correctly.
I have now added code to test whether a document in the "tables" container is a Query or a table, by testing whether there is a querydef or not. There ought to be a better way, but I don't think there is.
I came across this thread about .lastupdated returning incorrect values. This thread references KB299554, but this link now gives a 404 Error. According to the thread, MS never fixed this.
@gemma-the-husky
I followed your instructions exactly including all field names.
Just created the table again as a check. It still fails with the same error unless I include the error handling line
Queries starting ~sq are temp queries used for form / report record sources and for listbox / combo row sources
~sq_f = form ; ~sq_c = form control ; ~sq_r = report ; ~sq_d = report control
Whilst they are saved they should never appear in the nav pane - though I have seen it happen
Personally, I normally EXCLUDE them from all such lists unless I specifically want to see details of those items
More details of the 4 types of ~sq* queries here:
This article explains how to remove 'legacy' deleted objects from the read only MSysObjects system table if you have problems.
www.isladogs.co.uk
You could also consider EXCLUDING tables starting USys* (User created system tables) & ~TMPCLP* (deleted objects)
Deep hidden tables starting f_* don't appear anyway
I split the Tables container into tables and queries by checking the Type value in MSysObjects. If Type = 5 its a query, 1/4/6 are all Tables
Your approach is equally valid
The table description is a property that gets created when first populated for each item. It's a bit of a pain. I think you mentioned that already.
Tables use the tabledef.attributes value, which is a long value covering a number of options (summed)
Hidden is attribute 1
System is attribute -2147483646
You should be able to "and" them, although I am not sure how you "and" a negative. Edit. Maybe that negative is the large "sign" bit of the attributes word. It looks like 2^31 is that number, but with an 8 on the end, rather than a 6, and of course the number is negative.
I presume other objects queries, forms, reports etc use the same settings in their own object model.
Thanks but I'm aware of all that and already use it extensively
Those 2 enum values you quoted apply as the Flags values for deep hidden and system tabes in MSysObjects.
However depending on the properties of individual system tables, they can have a wide range of Flags values
MS haven't documented all of these
The meaning of the Flags values as an indicator of the table properties begin to make more sense using the Hex value.
This is a list of all the system tables in a typical database.
It also includes a test non-system table with an attachment field
www.isladogs.co.uk
. Also, you can't ignore the "~" objects if they are queries because Access prefixes all the temp querydefs it makes whenever we use an SQL string instead of the name of a querydef or table as the RecordSource or RowSource in forms and reports.
www.isladogs.co.uk
