How to compare MS-Access created and last modified dates of tables, queries, forms, etc.?

Question

I like to create a list when tables, queries, forms etc. were created and updated in an Access 2010 database.

In theory this is possible with a little VBA code but unfortunately this code shows the wrong information. I tested it myself and it is confirmed by Microsoft here: https://support.microsoft.com/en-us/kb/299554

Access shows the correct modified date on the Navigation Panel but it seems to be impossible to access this information via VBA or in any table. Some time ago I searched for this on the internet and a couple of people confirmed the problem but nobody had an answer.

Now my question: Does anybody know how to export the correct modified date information from Access (shown in the Navigation Panel)?

If this is impossible (my current research suggest this) does anyone know a reliable way to compare one database file to another and show all the differences in tables, queries, forms, etc.?

This is all about the design of the Access objects and not about any data stored in the tables.

score 5 · Accepted Answer · answered Jul 12 '15 at 13:58

That linked article describes why the LastUpdated property doesn't give you what you want for database objects such as forms and reports. Unfortunately it doesn't tell you that you can use DateModified from the appropriate CurrentProject collection for those.

For example, consider this screen capture of a form in the Navigation pane:

Navigation pane showing a form

When referencing that form in the Documents collection, LastUpdated and DateCreated both return the same value:

? CurrentDb.Containers("Forms").Documents("Form1").DateCreated
8/20/2012 10:51:07 PM 
? CurrentDb.Containers("Forms").Documents("Form1").LastUpdated
8/20/2012 10:51:07 PM

However DateModified for that same form in the CurrentProject.AllForms collection gives you the value which is displayed in the Navigation pane:

? CurrentProject.AllForms("Form1").DateModified
7/1/2015 6:47:40 AM

Note you can also get DateCreated via AllForms:

? CurrentProject.AllForms("Form1").DateCreated
8/20/2012 10:51:07 PM

Other CurrentProject collections include: AllMacros; AllModules; and AllReports. Beware that modules are saved together so DateModified shows you the time the project was last saved. That means each module will show that same time, the same as displayed in the Navigation pane.

Thanks, this will make my life a lot easier. I wonder why MS doesn’t publish this important information. — Edgar, Jul 13 '15 at 00:47

Ben · Answer 2 · 2019-07-23T14:55:37.267

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

Disclaimer: I am referencing the answer to a similar question that I had posted.

How to compare MS-Access created and last modified dates of tables, queries, forms, etc.?

2 Answers2