Getting the Creation Date/Time

So have you ever tried to retrieve the Created Date/Time. Pretty easy right as there are a couple ways to attack this!

You can simply query the MSysObjects table:

SELECT MSysObjects.DateCreate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or perhaps use DAO Containers and Documents:

CurrentDb.Containers("Forms").Documents("Form1").Properties("DateCreated").Value

Getting the Last Modified Date/Time

But what about the Modified Date/Time?

So you would think that we could simply modify the above query of the MSysObjects table to:

SELECT MSysObjects.DateUpdate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or the DAO Containers and Documents to:

CurrentDb.Containers("Forms").Documents("Form1").Properties("LastUpdated").Value

But those are not reliable and don’t always work properly! (especially for Forms and Reports). Instead, they seem to return the Created Date/Time again.
 

So What’s The Solution Then?

I won’t bore you with all the details, but we can simply build and use a function like:

Enum ObjectDateType
    ObjectDateCreated = 1
    ObjectLastModified = 2
End Enum

'---------------------------------------------------------------------------------------
' Procedure : db_GetObjectDate
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve either the Creation or Last Modification date of an Access Object
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' Dependencies: ObjectDateType Enum
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjectName       : Name of the object to retrieve the date value of
' lObjectType       : Type of abject (acTable, acQuery, acForm, acReport, ...)
' lObjectDateType   : Which date to retrieve (ObjectDateCreated or ObjectLastModified)
'
' Output:
' ~~~~~~~
' Returns a Date/Time value if the object is found and NULL otherwise.
'
' Usage:
' ~~~~~~
' Get the last modified date for an object
' ? db_GetObjectDate("Form1", acform)
'   Returns -> 11/29/2013 3:00:12 PM
' Get the creation date of an object
' ? db_GetObjectDate("Form1", acform, ObjectDateCreated)
'   Returns -> 10/17/2009 3:35:24 PM
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2009-10-17             
' 2         2023-01-24              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function db_GetObjectDate(sObjectName As String, lObjectType As AcObjectType, _
                                 Optional lObjectDateType As ObjectDateType = ObjectLastModified) As Variant
    On Error GoTo Error_Handler

    db_GetObjectDate = Null

    Select Case lObjectType
        Case acForm    '2467 - The expression you entered refers to an object that is closed or doesn't exist.
            If lObjectDateType = ObjectDateCreated Then
                db_GetObjectDate = CurrentProject.AllForms(sObjectName).DateCreated
            Else
                db_GetObjectDate = CurrentProject.AllForms(sObjectName).DateModified
            End If
        Case acMacro    '2467 - The expression you entered refers to an object that is closed or doesn't exist.
            If lObjectDateType = ObjectDateCreated Then
                db_GetObjectDate = CurrentProject.AllMacros(sObjectName).DateCreated
            Else
                db_GetObjectDate = CurrentProject.AllMacros(sObjectName).DateModified
            End If
        Case acModule    '2467 - The expression you entered refers to an object that is closed or doesn't exist.
            If lObjectDateType = ObjectDateCreated Then
                db_GetObjectDate = CurrentProject.AllModules(sObjectName).DateCreated
            Else
                db_GetObjectDate = CurrentProject.AllModules(sObjectName).DateModified
            End If
        Case acQuery    '3265 - Item not found in this collection.
            If lObjectDateType = ObjectDateCreated Then
                db_GetObjectDate = CurrentDb.QueryDefs(sObjectName).DateCreated
            Else
                db_GetObjectDate = CurrentDb.QueryDefs(sObjectName).LastUpdated
            End If
        Case acReport    '2467 - The expression you entered refers to an object that is closed or doesn't exist.
            If lObjectDateType = ObjectDateCreated Then
                db_GetObjectDate = CurrentProject.AllReports(sObjectName).DateCreated
            Else
                db_GetObjectDate = CurrentProject.AllReports(sObjectName).DateModified
            End If
        Case acTable    '3265 - Item not found in this collection.
            If lObjectDateType = ObjectDateCreated Then
                db_GetObjectDate = CurrentDb.TableDefs(sObjectName).DateCreated
            Else
                db_GetObjectDate = CurrentDb.TableDefs(sObjectName).LastUpdated
            End If
    End Select

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number <> 2467 And Err.Number <> 3265 Then
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Source: db_GetObjectDate" & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

Usage Example

Date Created

To retrieve the date an object was Created, we can simply do something like:

? db_GetObjectDate("Form1", acform, ObjectDateCreated)

which should then return a value similar to:

10/17/2009 3:35:24 PM

Date Last Modified

To retrieve the date an object was Last Modified, we can simply do something like:

? db_GetObjectDate("Form1", acform)

OR

? db_GetObjectDate("Form1", acform, ObjectLastModified)

which should then return a value similar to:

11/29/2013 3:00:12 PM

Where’s Microsoft In All Of This?

I have no clue to tell you the truth!

This issue has been around for a really long time 15+ years, maybe even since Access first came out for all I know. It has been flagged in the past and I have posted it as a suggestion in the Microsoft Access Community Feedback Portal to put it back on the radar, so feel free to upvote the suggestion as they should easily be able to fix this with minimal effort!

Fix the Containers Documents LastUpdated Value Bug

It would be nice when querying this property that is returned the proper value.

Useful Resources

ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time

The Data Access Objects (DAO) LastUpdated property returns incorrect dates/times for Microsoft Access forms, reports, macros, and modules. Microsoft Access does not notify the Microsoft Jet database engine about the modification of Access-specific...

Containers collection (DAO)

A Containers collection contains all of the Container objects that are defined in a database.

Documents collection (DAO)

A Documents collection contains all of the Document objects for a specific type of object (Microsoft Access database engine databases only).