How to compare MS-Access and last modified dates of tables, queries, forms, etc.?
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 incorrect information. I tested it myself and it's confirmed by Microsoft here: https://support.microsoft.com/en-us/kb/299554
Access shows the correct modified date in the navbar, but it doesn't seem to be possible to access that information via VBA or any table. I searched the internet for this a while ago and several people confirmed this issue but no one answered.
Now my question is: Does anyone know how to export the correct modified date information from Access (shown in the navigation bar)?
If this is not possible (my current research shows this), does anyone know of a reliable way to compare one database file to another and show all the differences in tables, queries, forms, etc.?
It's all about the design of Access objects, not any data stored in tables.
source to share
This article describes why the property LastUpdated
doesn't give you what you want for database objects such as forms and reports. Unfortunately, it doesn't tell you what you can use DateModified
from the appropriate collection CurrentProject
for them.
For example, consider this screen capture of a form in a navigation bar:
When referencing this 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 the same form, the collection CurrentProject.AllForms
gives you the value that is displayed in the Navigation Pane:
? CurrentProject.AllForms("Form1").DateModified 7/1/2015 6:47:40 AM
Note that you can also get DateCreated
through AllForms
:
? CurrentProject.AllForms("Form1").DateCreated 8/20/2012 10:51:07 PM
Other collections CurrentProject
include AllMacros
:; AllModules
; and AllReports
. Remember that modules are saved together, therefore it DateModified
shows the time at which the last project was saved. This means that each module will show the same time as in the navigation bar.
source to share
Here is a function that will fetch the correct information (except 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 referring to the answer to a similar question that I posted.
source to share