Solved Determining last update date of all objects

[gemma-the-husky]

@isladogs
The numbers I quoted for the attributes were taken from my DAO object model book, which also lists a lot more values. I must say I thought it was fully enumerated in the resource. I can send you the details if you are interested. [edit. I checked my reference book, and the negative number I quoted ends with a 6, whereas you show it ending with an 8, so it must be a typo in the book]

There were a couple of containers it talks about where it says they have no information.

[edited]
Regarding the 3127 error. The message you displayed said no field called "dateupdated", and I didn't get that error in any version, so I just assumed you hadn't created that field. However I can see you did, and I can also that the dateupdated values are getting populated, so I don't understand the error. As I say, I didn't get that error in any version.

[further edit]
Generally, given that the navigation pane shows distinct last modified dates for forms and reports, they must be stored somewhere, but if it's not documented where, it's tricky. It doesn't show distinct dates for modules, so maybe it uses the database modified date as the modified date for modules. Something like that.

 

[gemma-the-husky]

@Pat Hartman
That stack overflow item I linked to included a note that the created and lastupdated date of forms and reports could be obtained from fields in the AllForms and AllReports sections of CurrentProject, rather than from the container.

It says it doesn't work for modules.

I checked this for a report and it works. Note that the field here is DateModified rather than LastUpdate.

MsgBox "Created: " & CurrentProject.AllReports(doc.Name).DateCreated & vbCrLf & _
"Modified: " & CurrentProject.AllReports(doc.Name).DateModified

I changed AllReports to AllModules, and the DateModified does show the date that is in the navigation pane. With modules, all the modules seem to show the same last updated date, so maybe there is no separate date for each module available. When you save module changes, the process updates the last modified date for all modules, and MS never bothered to fix it. I think that is more likely than there being another hidden value stored somewhere else.

----------------------------------------------------------------------------------------------------------------------------------------

This is what @kentgorrell reported earlier. I imagine a good way now would be to use his suggestion and hash the contents of each module.. If you get a different result to the last time, then store the new date/time for the updated value. @kentgorrell - How do you create the hash total.
You could even output the old versions as .bas units in a folder somewhere, as an archive store of changes.

I think having a single table of all the objects in the database which can be sorted and filtered might well be an improvement on the navigation pane. It's often hard to find things in the Navigation pane, I find.

 

[isladogs]

@isladogs
The numbers I quoted for the attributes were taken from my DAO object model book, which also lists a lot more values. I must say I thought it was fully enumerated in the resource. I can send you the details if you are interested. [edit. I checked my reference book, and the negative number I quoted ends with a 6, whereas you show it ending with an 8, so it must be a typo in the book]

There were a couple of containers it talks about where it says they have no information.

[edited]
Regarding the 3127 error. The message you displayed said no field called "dateupdated", and I didn't get that error in any version, so I just assumed you hadn't created that field. However I can see you did, and I can also that the dateupdated values are getting populated, so I don't understand the error. As I say, I didn't get that error in any version.

[further edit]
Generally, given that the navigation pane shows distinct last modified dates for forms and reports, they must be stored somewhere, but if it's not documented where, it's tricky. It doesn't show distinct dates for modules, so maybe it uses the database modified date as the modified date for modules. Something like that.

@gemma-the-husky
Hi Dave
1. I am aware of the following TableDefAttributeEnum enumeration values:

Name	Value	Description
dbAttachedODBC	536870912	Linked ODBC database table.
dbAttachedTable	1073741824	Linked non-ODBC database table.
dbAttachExclusive	65536	Opens a linked Microsoft Access database engine table for exclusive use.
dbAttachSavePWD	131072	Saves user ID and password for linked remote table.
dbHiddenObject	1	Hidden table (for temporary use).
dbSystemObject	-2147483646	System table.

If you have any additional info, I would be very grateful

2. Originally I did mis-type the field name DateUpdated in the table.
It was for that reason that I re-created the table and tested again - the error still occurred for the first item MSysDb

3. I'm equally baffled. The property values and MSysObjects values are the same for some types of object but not others. I can't think of any further ways of investigating this

 

[isladogs]

@Pat Hartman
That stack overflow item I linked to included a note that the created and lastupdated date of forms and reports could be obtained from fields in the AllForms and AllReports sections of CurrentProject, rather than from the container.

It says it doesn't work for modules.

I checked this for a report and it works. Note that the field here is DateModified rather than LastUpdate.

MsgBox "Created: " & CurrentProject.AllReports(doc.Name).DateCreated & vbCrLf & _
"Modified: " & CurrentProject.AllReports(doc.Name).DateModified

I changed AllReports to AllModules, and the DateModified does show the date that is in the navigation pane. With modules, all the modules seem to show the same last updated date, so maybe there is no separate date for each module available. When you save module changes, the process updates the last modified date for all modules, and MS never bothered to fix it. I think that is more likely than there being another hidden value stored somewhere else.

----------------------------------------------------------------------------------------------------------------------------------------

This is what @kentgorrell reported earlier. I imagine a good way now would be to use his suggestion and hash the contents of each module.. If you get a different result to the last time, then store the new date/time for the updated value. @kentgorrell - How do you create the hash total.
You could even output the old versions as .bas units in a folder somewhere, as an archive store of changes.

I think having a single table of all the objects in the database which can be sorted and filtered might well be an improvement on the navigation pane. It's often hard to find things in the Navigation pane, I find.

Hi
The first half of this is the approach that I have been using for a variety of purposes

You could of course use Application.SaveAsText to save any db object (except tables for some reason) to text files then grab the date created or date modified property of that file back into an Access table

However, whilst I have created tables of db objects in the past, I am adverse for doing that when the MSysObjects table already contains that info.
For example, I have an app which pulls a list of queries from MSysObjects & determines the last saved view & last saved date for each by exporting to text files

This info is then used in another form to view/edit queries in SQL, design & datasheet view on the same form.
For example:

The idea is to provide functionality similar to SQL Server Management Studio
@CJ_London is working on something similar (but much more ambitious in its aims) as part of his excellent Access Studio project

Access Studio

Access Studio adds the functionality of SQL Server Management Studio to Access. Used as a reference library, it allows users to quickly switch between Access apps, use formatted SQL, run parts of more complex SQL code, add comments, see results in the same development window etc.

www.isladogs.co.uk

 

[Pat Hartman]

There is only ONE reason I am trying to find the last update date and that is so that I can tell if something has changed since the last backup. It would be downright silly to have to export all objects to text one at a time for hundreds of objects to determine if anything changed since my last backup. Part of the backup is the ability to export to text OR to just make a copy of the whole db. If I wanted to know with accuracy, the last time form1 changed, that is one thing but so far no solution actually works. The non-MSysObjects solution all require running code to examine all objects and load the dates into a table or an array. If those actually worked, I would use one but they don't so thanks to everyone but I'll just do what I am currently doing which is try to remember to update some DAO object to force the date to change accurately.

 

[isladogs]

In my opinion, more than one solution should work well enough for the purpose you described
The obvious exception is modules which are all updated if one changes.

So, why not run the code (without saving to a table), then save all the modules & any other changed objects as text files.
Tables can't be saved as text files using SaveAsText but can be exported as XML

 

[Pat Hartman]

The solution using the MSysObjects is far and away the simplest. It is no worse than the others which also don't get the dates right in all cases. So, I can waste CPU cycles looping through hundreds of objects in a half dozen collections to get the wrong answer and cause bloat by saving info in a table or array or I can use a simple query of MSysObjects to get the wrong answer. At least if I am conscious enough to know that I changed something and want a backup, I can force the issue by also updating my MSysObjects query and that makes it work because a change to any DAO object is always correct in the MSysObjects table.

Thanks for all the help. It looks like "last update date" mystery is a problem that MS needs to solve unless they're too busy doing stupid, useless stuff Hopefully, if they deign to solve the problem, they choose to make MSysObjects accurate since that is the most accessible source.

The underlying problem is caused by poor programming discipline. We lecture constantly about why it is wrong to store the same information in multiple places. Here is absolute evidence of why this is so wrong.

 

[gemma-the-husky]

@gemma-the-husky
Hi Dave
1. I am aware of the following TableDefAttributeEnum enumeration values:

Name	Value	Description
dbAttachedODBC	536870912	Linked ODBC database table.
dbAttachedTable	1073741824	Linked non-ODBC database table.
dbAttachExclusive	65536	Opens a linked Microsoft Access database engine table for exclusive use.
dbAttachSavePWD	131072	Saves user ID and password for linked remote table.
dbHiddenObject	1	Hidden table (for temporary use).
dbSystemObject	-2147483646	System table.

If you have any additional info, I would be very grateful

2. Originally I did mis-type the field name DateUpdated in the table.
It was for that reason that I re-created the table and tested again - the error still occurred for the first item MSysDb

3. I'm equally baffled. The property values and MSysObjects values are the same for some types of object but not others. I can't think of any further ways of investigating this

1. Your table values are the same ones I have.
I assumed the last one was wrong, as 2^31 is the same number, positive but with an 8 at the end, but you have it with a 6 as well. Maybe it's the negative value/twos complement of the same positive number.

2. Strange. I didn't get an error with MSysDB

 

[The_Doc_Man]

I have been scrupulously avoiding this discussion for a while. (There are those who will question whether I have EVER been scrupulous, but... I digress.)

Pat (in post #47) has reached the correct conclusion. If you want something done right, do it yourself. This is actually part of my "Old Programmer's Rule #2" - Access can't tell you anything you didn't tell it first, or at least tell it how to find out.

It is a rule in most operating systems as well as most general utilities (and I'm classing all of Office in this category) that they give you tools but if you want something specific, you might have to work in a way to assure that you can get it based on your own efforts. OpenVMS and most UNIX systems are similarly designed because to be honest, tracking lots of incidentals can lead to a lot of overhead. And overhead in an operating system or utility program is a drag on performance, something to be abhorred. IBM, Digital Equipment Corp., Microsoft, and Bell Labs ALL got gigged (back in the day) for the minimalist nature of their offerings. Their answer was always "We gave you enough for you add to functionality if you wanted or needed to." I.e. you want it bad enough, do it yourself.

 

[CJ_London]

just i response to Colins mention in post#44 re Access Studio the new navigation form has the option to orders databases and tables/queries by 'last modified' date. It doesn't provide modules at this time and not quite finished but will look a bit like this

 

[kentgorrell]

The process I use is more about version history but it's kinda the same thing and I will run it sometimes when I just want a backup even though I'm not releasing.

The process documents all the objects with Created and Modified dates, and Version Number then exports to text if they are new or changed. This info is stored in FE tables, one for each object type as the attributes for each type vary too much to use a single table. In building this process, I found that the methods for documenting each object type vary, although forms and reports are very similar. MS do not make it easy.

I store the full version history in a linked accdb because it is only needed in the development environment and doesn't need to be distributed. This allows me to easily find an old version of any object and do a compare to the current and/or import it back from the text file.

So I run this process before every release and I have a full version history.

Recently I tried to modify the process to handle the migration of all objects to a new container with only limited success. The main obstacle was comparing Created and Modified dates to the date of the migration.

@Gemma the Husky wrote -

If doc.LastUpdated <> lastdate failed to work correctly
I had to use this
If Abs(DateDiff("s", doc.LastUpdated, lastdate)) > 0 Then

and I think this was the issue.

For module comparison I store the hash value and compare

                Set mdl = VBE.ActiveVBProject.VBComponents(strModuleName).CodeModule
                strCode = mdl.Lines(1, mdl.CountOfLines)
                strHash = MD5Hex_String(strCode)
                blnModified = Not (strHash = !Code_Hash & "")

Public Function MD5Hex_String(str As String) As String
Dim enc
Dim bytes() As Byte
Dim strOut As String
Dim iPos As Integer
    Set enc = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    ' Convert the string to a byte array and hash it
    bytes = StrConv(str, vbFromUnicode)
    bytes = enc.ComputeHash_2((bytes))
    ' Convert the byte array to a hex string
    For iPos = 0 To UBound(bytes)
        strOut = strOut & LCase(Right("0" & Hex(bytes(iPos)), 2))
    Next
    MD5Hex_String = strOut
    Set enc = Nothing
End Function

One other thing to think about for PT queries: if the only thing that's changed are the parameters, I don't class this as modified so I test to see if anything before '@" has changed.

 

[kentgorrell]

My ultimate objective in listing objects was to be able to list object dependencies without using Track Name Autocorrect (for obvious reasons). This is rather useful.

To achieve this, the process
• first updates the lists of objects.
• Then searches only objects that have changed to list which other objects it uses.
Obviously, to accomplish this one must have very good naming conventions.

The result is that I can view an object and immediately see a list of other objects it depends on.
or a list of other object where it is used
and, if it isn't used I can delete it knowing that I can still get it back from the text file if needed.

You can see an example of this in the Access User Group (Pacific) talk I gave in October 2021
Titled: Automating the Software Development Lifecycle

You'll find dependencies in about 27 minues.

That's just in case you're bored and have nothing better to do.

 

[Pat Hartman]

This thread has taken many twists and turns and many people have offered suggestions. However, there is no solution which has been offered that accurately provides the date I was searching for.

Thank you again for the ideas but unless someone comes up with a solution to accurately obtain the latest change date for ANY object, I'm done.

 

[Pat Hartman]

@isladogs I don't think so, the code you posted has exactly the same problem. Looping through the collections one of the alternative methods I had tried. The code Crystal uses in her documenter also also uses the collections and so doesn't work. The collections have the same problem as the MySysObjects table. I don't have a link to the old thread but I did post examples. I think no one ever was able to figure out how to address the data behind the Navigation Pane.

 

[isladogs]

@isladogs I don't think so, the code you posted has exactly the same problem. Looping through the collections one of the alternative methods I had tried. The code Crystal uses in her documenter also also uses the collections and so doesn't work. The collections have the same problem as the MySysObjects table. I don't have a link to the old thread but I did post examples. I think no one ever was able to figure out how to address the data behind the Navigation Pane.

Sorry but I disagree. In my tests, the properties were always identical to that shown in the navigation pane

 

[Pat Hartman]

@isladogs Make sure you test the dates on an unbound form. Or a bound form where you only changed a visual property but nothing to do with the RecordSource or a ControlSource.

 

[isladogs]

@isladogs Make sure you test the dates on an unbound form. Or a bound form where you only changed a visual property but nothing to do with the RecordSource or a ControlSource.

I had already tested both of those scenarios. Both work correctly exactly as I described both above in posts #5 & #11 and in the original thread
Solved - Determining last update date of all objects | Access World Forums (access-programmers.co.uk)

I've just tested again to confirm:

Changed background colour of unbound Form1 and ran code again:

As you can see, it works
As already mentioned, the only issue is that all modules have the same date modified value.
Access treats them all as a single object in this respect for some reason.

 

[Pat Hartman]

@isladogs Are you using a bound form? Did you modify anything having to do with DAO? That is where the disconnect is. It is easier to see with an unbound form, (unless it's been fixed this month) If you don't mind, can you send the test database so I can see the code you are using? Thanks. What I found was queries get dated correctly but forms/reports only get dated correctly if you change the RecordSource. I don't use macros so I don't know if they have the same issues. Modules, all get the same date. If you update one, they all show the same date. I'll see if I can find the database I was testing with. I'm sure I put it in a "safe" place so of course I can't find it.

 

[Pat Hartman]

I found the database I created to show the problem and a solution. It requires getting the dates for tables and queries from the Containers and getting the dates for Forms/Report/Macros from the "All" collections. Seems that the last changed date is hopeless for modules since Access comingles the code and even though it seems to be able to differentiate the create dates correctly, the Update Date is always the same for all modules. I made some more comments and one bug fix so I uploaded a new version.

The sample uses a temp table so I could see what I was working with. However deleting/refilling a temp table leads to bloat so this isn't a good solution for the problem I was trying to solve so. When I use it in the way I intended to use it which was to determine if I (the developer) had made any change to the FE since the last backup, I run the code and instead of logging to a table, I keep some variables in memory. I'm looking for the highest date so in the loop that goes through the Collections to pick up all the objects, I log the name the type and the last update date and if the next object has a newer update date, I replace all three variables. That is what gets displayed on my switchboard but only when I am the logged in user. The users just see the version number of the released FE.

The whole point of this exercise in case it was unclear earlier (and it must have been given the responses I got) is that I wanted the code to prompt ME, the Developer (not the user) whenever I closed the database to prompt to back up if there was a change since the last backup and to display the name of the changed object. When you are on a roll developing, it is easy to loose track of time and forget to back up your work. This method lets me just close the app and say yes which means that I am much more likely to have a good backup position if I break something when I am making a change.

 

[isladogs]

@Pat Hartman
As far as I'm concerned, the issue of getting the correct metadata has been solved. Your last post basically describes what I've been saying & doing for some time...except I'm also saving the date created and description properties to a table because that suits my purposes.

For your purposes, you only need to store the date/time of last backup in a table NOT the date modified values.
Run code to check date modified either at startup or just before close. However as you loop through, just save the newest date modified value as a variable or tempvar. If that value is newer than the last backup then a prompt to run a backup should be shown. When it runs, save the date/time in a table

EDIT:

@isladogs Are you using a bound form? Did you modify anything having to do with DAO? That is where the disconnect is. It is easier to see with an unbound form, (unless it's been fixed this month) If you don't mind, can you send the test database so I can see the code you are using? Thanks. What I found was queries get dated correctly but forms/reports only get dated correctly if you change the RecordSource. I don't use macros so I don't know if they have the same issues. Modules, all get the same date. If you update one, they all show the same date. I'll see if I can find the database I was testing with. I'm sure I put it in a "safe" place so of course I can't find it.

As already stated, I tested on an unbound form.... in fact on more than one including a form with no code whatsoever.
As shown in post 57, I tested on all database objects including a macro created for the purpose
I thought I had already uploaded my code a few weeks ago but perhaps my memory is faulty on this.
Going out for a few hours but will do so later today.