如何从Access中的对象获取准确的LastUpdated日期/时间? [英] How to get accurate LastUpdated date/time from objects in Access?
问题描述
我试图从Access对象中检索 LastUpdated 日期,有时它返回的是DateCreated值.
I am trying to retrieve the LastUpdated date from Access objects and sometimes it is returning the DateCreated value.
我在查询 MSysObjects 时看到相同的结果:
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;
或在即时"窗口中使用 DAO :
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:
我正在使用32位Access 2016 Office 365.
I am using Access 2016 Office 365, 32-bit.
推荐答案
在撰写此问题时进行研究时,我发现这是很久以前的一个已知错误(至少是Access 2007).
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).
虽然微软没有修复它令人失望,但是还有另一种获取准确信息的方法.
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
如果要在SQL中调用此函数,建议您在选择所有对象之前进行过滤,否则会很慢.
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
这篇关于如何从Access中的对象获取准确的LastUpdated日期/时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!