在写这个问题的同时进行研究时,我发现这是很久以前的一个已知错误(至少是 Access 2007)。
KB 299554:数据访问对象 (DAO) LastUpdated 属性在 Microsoft Access 数据库中返回不正确的日期/时间 https://support.microsoft.com/en-us/help/299554/the-data-access-objects-dao-lastupdated-property-returns-incorrect-dat
虽然微软没有修复这个问题令人失望,但还有另一种方法可以获取准确的信息。
这是一个将检索正确信息(模块除外)的函数:
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
如果你想在 SQL 中调用这个函数,我建议你在选择所有对象之前进行过滤,否则速度会很慢。
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