How to get accurate LastUpdated date/time from obj

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.

Answer 1

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.

Answer 2

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