How to get accurate LastUpdated date/time from objects in Access?

Question

I am trying to retrieve the LastUpdated date from Access objects and sometimes it is returning the DateCreated value.

I am seeing the same results querying MSysObjects:

SELECT MSysObjects.Name, 
    Switch([Type]=5,'Query',[Type]=-32768,'Form',[Type]=-32764,'Report',[Type]=-32766,'Macro',[Type]=-32761,'Module') AS ObjectType, 
    MSysObjects.DateUpdate 
FROM MSysObjects
WHERE (((Left$([Name],1))<>'~') AND ((MSysObjects.Type) In (5,-32768,-32764,-32766,-32761)))
ORDER BY MSysObjects.DateUpdate DESC;

or using DAO from the Immediate window:

? CurrentDb.Containers("Forms").Documents("frm_POC_Assignment_Override").LastUpdated

The correct date is shown in the Navigation Pane (if you select View By | Details)

and appears in the object properties dialog:

I am using Access 2016 Office 365, 32-bit.

For what reason do need that information? In rare cases I sort the db objects by modified date to find one, but I have no clue for what else that could be used. — ComputerVersteher, Jul 25 '19 at 20:01
The first reason is for providing support of a database I haven't touched in a while. It's helpful to sort the objects by date because often the object with the most recent date is where I need to focus. This date helps me find archives and emails that relate to it. The second reason is I have built some Access add-ins as developer tools. I support Access applications I have developed since 2000 that were deployed with various versions of standard form and code modules. It's helpful to see the modified date to find the latest version of that object. The wrong date shown is frustrating. — Ben, Jul 30 '19 at 19:12
Thanks for response, that explains your needs. You should add that to question. Can't you work around by using a vcs ([msaccess-vcs-integration](https://github.com/timabell/msaccess-vcs-integration) helps on export), that shows the dates. — ComputerVersteher, Aug 08 '19 at 21:51

Ben · Accepted Answer · 2019-07-23T14:54:11.053

In doing research while writing up this question I found out that this is a known bug from a long time ago (at least Access 2007).

KB 299554: The Data Access Objects (DAO) LastUpdated property returns incorrect dates/times in Microsoft Access database

While it's disappointing that Microsoft hasn't fixed it, there is another way to get the accurate information.

Here is a function that will retrieve the correct information (except for modules):

Public Function fGetObjectModifiedDate(Object_Name As String, Object_Type As Integer) As Variant
' Get the correct Modified Date of the passed object.  MSysObjects and DAO are not accurate for all object types.

' Based on a tip from Philipp Stiefel <https://codekabinett.com>
' Getting the last modified date with this line of code does indeed return incorrect results.
'   ? CurrentDb.Containers("Forms").Documents("Form1").LastUpdated
'
' But, that is not what we use to receive the last modified date, except for queries, where the above line is working correctly.
' What we use instead is:
'   ? CurrentProject.AllForms("Form1").DateModified

    Select Case Object_Type
        Case 5 ' Query
            fGetObjectModifiedDate = CurrentDb.QueryDefs(Object_Name).LastUpdated
        Case -32768 ' Form
            fGetObjectModifiedDate = CurrentProject.AllForms(Object_Name).DateModified
'            fGetObjectModifiedDate = CurrentDb.Containers("Forms").Documents(Object_Name).LastUpdated
        Case -32764 ' Report
            fGetObjectModifiedDate = CurrentProject.AllReports(Object_Name).DateModified
        Case -32766 ' Macro
            fGetObjectModifiedDate = CurrentProject.AllMacros(Object_Name).DateModified
        Case -32761 ' Module
            ' This will report the date that *ANY* module was last saved.
            ' The CurrentDb.Containers method and MSysObjects will report the date created.
            fGetObjectModifiedDate = CurrentProject.AllModules(Object_Name).DateModified
        Case Else
            ' Do nothing.  Return Null.
    End Select

End Function

If you want to call this function in SQL I suggest that you filter before selecting all objects or it will be slow.

SELECT MSysObjects.Name, 
    Switch([Type]=5,'Query',[Type]=-32768,'Form',[Type]=-32764,'Report',[Type]=-32766,'Macro',[Type]=-32761,'Module') AS [Object Type], 
    MSysObjects.DateUpdate, 
    fGetObjectModifiedDate([Name],[Type]) AS DateModified
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "frm_POC_Assign*") 
AND ((Left$([Name],1))<>'~') AND ((MSysObjects.Type) In (5,-32768,-32764,-32766,-32761)))
ORDER BY MSysObjects.Name

Just keep in mind, that saving any stand alone code module will cause ALL of the updated status for all code modules to become the same. Other then that, you have a good solution here. — Albert D. Kallal, Jul 18 '19 at 23:17

score 0 · Answer 2 · answered Jul 25 '19 at 19:43

well just another minor comment to this topic. Access is a good multi user application, but isn't really striving to be a multi-developer management studio.... which is the only reason you would need to time stamp fixed objects design changes.

in most cases one definitely does not want users changing objects - and the compiled .accde is all that is released, so they do not have that ability.

where the user base has the skills to craft their own queries - it often is best to set up a separate front end specifically for this purpose and keep them out of the main application's object navigation pane.

Comments like this should be entered as comments and not posted as an answer. — Ben, Jul 30 '19 at 18:49

How to get accurate LastUpdated date/time from objects in Access?

2 Answers2

Linked