Modified Date (All versions)

Topic

New Reply

If you use the “Details” view of the database window, you get several columns of data for each object. Two of the columns are “Modified” and “Created”, which are the date/time each of these actions last occurred. It is NOT the “LastUpdated” and “DateCreated” properties of the object; nor is it the “DateCreate” and “DateUpdate” fields in MsysObjects. I can’t seem to find them anywhere.

Does anyone know where this information is stored?

Reply | Quote

Replies

I don’t know if this clarifies anything, here is some sample results I got in a copy of Northwind:

‘ DAO object properties:
? CurrentDB.Containers(“Forms”).Documents(“Form1”).DateCreated
12/28/2002 06:29:34 PM
? CurrentDB.Containers(“Forms”).Documents(“Form1”).LastUpdated
12/28/2002 06:36:34 PM

From Help:
DateCreated, LastUpdated Properties (applies to DAO Document object)
DateCreated returns the date and time that an object was created, or the date and time a base table was created if the object is a table-type Recordset object (Microsoft Jet workspaces only).

LastUpdated returns the date and time of the most recent change made to an object, or to a base table if the object is a table-type Recordset object (Microsoft Jet workspaces only).

Using ADO AccessObject properties for same object, got different results:

? CurrentProject.AllForms.Item(“Form1”).DateCreated
11/20/2003 03:41:03 PM
? CurrentProject.AllForms.Item(“Form1”).DateModified
11/20/2003 03:41:03 PM

These date/times reflect same info that is displayed in the db window, which reflects date/time the db was last compacted.

From Help:
DateCreated Property (applies to: AccessObject Object)
Returns a Date indicating the date and time when the design of the specified object was last modified. Read-only.

DateModified Property (applies to: AccessObject Object)
Returns a Date indicating the date and time when the design of the specified object was last modified. Read-only.

Testing with a linked table, the DAO and ADO objects returned same results:

DAO:
? CurrentDB.Containers(“Tables”).Documents(“Orders”).DateCreated
11/19/2003 08:51:45 AM
? CurrentDB.Containers(“Tables”).Documents(“Orders”).LastUpdated
11/19/2003 08:51:45 AM

? CurrentDB.TableDefs(“Orders”).DateCreated
11/19/2003 08:51:45 AM
? CurrentDB.TableDefs(“Orders”).LastUpdated
11/19/2003 08:51:45 AM

ADO:
? CurrentData.AllTables.Item(“Orders”).DateCreated
11/19/2003 08:51:45 AM
? CurrentData.AllTables.Item(“Orders”).DateModified
11/19/2003 08:51:45 AM

Note in this case all date/times reflect same date/time as displayed in db window, which seems to be date/time the table’s link was last refreshed via Linked Table Manager.

Not sure what to conclude from all this, other than that the DAO Document object DateCreated & LastUpdated properties may be more accurate indicators of when the object was actually created/modified than the data displayed in db window.

HTH

Reply | Quote

Testing some more, it appears the AccessObject DateModified property would be more accurate for modified objects. I made a design change to Form1 and got these results:

‘ DAO
? CurrentDB.Containers(“Forms”).Documents(“Form1”).DateCreated
12/28/2002 06:29:34 PM
? CurrentDB.Containers(“Forms”).Documents(“Form1”).LastUpdated
12/28/2002 06:36:34 PM

‘ ADO
? CurrentProject.AllForms.Item(“Form1”).DateCreated
11/20/2003 03:41:03 PM
? CurrentProject.AllForms.Item(“Form1”).DateModified
11/23/2003 04:04:32 PM

The DAO LastUpdated property does not reflect the modification. There’s an explanation for this behavior in MSKB Article 299554:

ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time

The article states in part: “Microsoft Access does not notify the Microsoft Jet database engine about the modification of Access-specific objects (forms, reports, macros, and modules); therefore, the DateUpdate column in the MSysObjects table is never updated to the correct date and time.” And: “Additionally, you may have noticed that the Database window displays the correct date and time for an object’s last modification. This is because Microsoft Access uses its own internal mechanism for storing the date created and date modified, independent of the Jet database engine. Unfortunately, Microsoft Access 2000 does not currently expose this information in its object model; therefore, there is no programmatic way to get to this information in Microsoft Access 2000.” But the AccessObject properties seem to reflect the same info as db window….

When I compacted db got same results as above (Access 2002), date/times did not change. Not sure if this is same in earlier versions of Access. In the db I’m using, almost all object dates reflect 11/20/2003, which was probably last time I compacted db when it was open in A2K rather than ACC 2002.

HTH

Reply | Quote

? CurrentProject.AllForms.Item(“Form1”).DateCreated

Mark,

I don’t even get “Date Created” to come up in the hint box. If it try a .properties.count, I get an error box that “the item is open or doesn’t exist”!

Reply | Quote

What version of Access are you using? I neglected to mention, the CurrentProject and CurrentData and AccessObjects collections such as AllForms, AllTables, etc were introduced in ACC 2000. The syntax I used worked OK in ACC 2002, it should work in A2K too? Also, note some limitations when using these objects as noted in MSKB Article 209788:

ACC2000: Properties Collection Returns Error for CurrentProject and CurrentData Objects

This may or may not explain the error you got. For example, if I typed:

? CurrentProject.AllForms.Item(“Form1”).Properties(“DateModified”)

I got Error 2455, “You entered an expression that has an invalid reference to property “DateModified.” The only time I got Error 2467 (object closed or doesn’t exist) was when I typed:

? CurrentProject.AllForms.Item(“Form1”).CurrentView

when form was closed. If form open the above instruction returned 0 (design) or 1 (running).

Reply | Quote

I’m using Access2000. I can enter?
?currentproject.Allforms.item(“frmMyForm”).name
And it will return “frmMyForm”. But the LastUpdated is not on the list, and produces an error if I try it.

Reply | Quote

Actually, the AccessObject property is DateModified, LastUpdated is the DAO property. But if you are getting error with DateModified, then must be some bug in A2K, in ACC 2002 works OK. I’m beginning to think the only reliable way to keep track of the date last modified, etc, for db objects is to create a table and keep track of it yourself.

Reply | Quote

When I type in this in immediate window:
? CurrentProject.AllModules.Item(“Module1”).
I get a hint list containing: FullName, IsLoaded, Name, Parent, Properties, and Type. Neither DateModified nor LastUpdated is on the list.

Reply | Quote

They exposed some additional properties in Access 2002. Don’t keep beating your head against it, because you won’t be able to find properties that simply were’nt there in A2000. Those two properties are only available in 2002.

Reply | Quote

>>They exposed some additional properties in Access 2002. <<

Charlotte,

Well, that explains alot. Thanks!

Reply | Quote

I discovered it the hard way.

Reply | Quote

I discovered it the hard way.

Reply | Quote

>>They exposed some additional properties in Access 2002. <<

Charlotte,

Well, that explains alot. Thanks!

Reply | Quote

They exposed some additional properties in Access 2002. Don’t keep beating your head against it, because you won’t be able to find properties that simply were’nt there in A2000. Those two properties are only available in 2002.

Reply | Quote

When I type in this in immediate window:
? CurrentProject.AllModules.Item(“Module1”).
I get a hint list containing: FullName, IsLoaded, Name, Parent, Properties, and Type. Neither DateModified nor LastUpdated is on the list.

Reply | Quote

Actually, the AccessObject property is DateModified, LastUpdated is the DAO property. But if you are getting error with DateModified, then must be some bug in A2K, in ACC 2002 works OK. I’m beginning to think the only reliable way to keep track of the date last modified, etc, for db objects is to create a table and keep track of it yourself.

Reply | Quote

I’m using Access2000. I can enter?
?currentproject.Allforms.item(“frmMyForm”).name
And it will return “frmMyForm”. But the LastUpdated is not on the list, and produces an error if I try it.

Reply | Quote

What version of Access are you using? I neglected to mention, the CurrentProject and CurrentData and AccessObjects collections such as AllForms, AllTables, etc were introduced in ACC 2000. The syntax I used worked OK in ACC 2002, it should work in A2K too? Also, note some limitations when using these objects as noted in MSKB Article 209788:

ACC2000: Properties Collection Returns Error for CurrentProject and CurrentData Objects

This may or may not explain the error you got. For example, if I typed:

? CurrentProject.AllForms.Item(“Form1”).Properties(“DateModified”)

I got Error 2455, “You entered an expression that has an invalid reference to property “DateModified.” The only time I got Error 2467 (object closed or doesn’t exist) was when I typed:

? CurrentProject.AllForms.Item(“Form1”).CurrentView

when form was closed. If form open the above instruction returned 0 (design) or 1 (running).

Reply | Quote

? CurrentProject.AllForms.Item(“Form1”).DateCreated

Mark,

I don’t even get “Date Created” to come up in the hint box. If it try a .properties.count, I get an error box that “the item is open or doesn’t exist”!

Reply | Quote

As for compacting database, in A2K this does reset the date created/modified. According to MSKB 244642, ACC2000: Created and Modified Dates for Objects Suddenly Reset:

SYMPTOMS
When you view the details of the objects in the Database window, you see that all forms, reports, macros, and modules have the same created and modified date. However, tables and queries have the actual date when they were created or modified.

CAUSE
This behavior occurs when you compact a database. Compacting a database resets the Created and Modified columns for all Access project items to the date and time when you compact the database. Access project items consist of all forms, reports, macros, and modules.

MS goes on to acknowledge that “this is a problem in Microsoft Access 2000.” It appears that this does not occur in Microsoft Access 2002. Another factor to take into account….

Reply | Quote

One additional note: if you compile the VBA project in VB Editor, the DateModified for all modules will reflect date/time you compiled the project:

? CurrentProject.AllModules.Item(“Module1”).DateModified
11/23/2003 06:47:42 PM

All modules in the db reflect the same (new) date/time, whether or not you changed any code in module prior to compiling project.

Reply | Quote

One additional note: if you compile the VBA project in VB Editor, the DateModified for all modules will reflect date/time you compiled the project:

? CurrentProject.AllModules.Item(“Module1”).DateModified
11/23/2003 06:47:42 PM

All modules in the db reflect the same (new) date/time, whether or not you changed any code in module prior to compiling project.

Reply | Quote

As for compacting database, in A2K this does reset the date created/modified. According to MSKB 244642, ACC2000: Created and Modified Dates for Objects Suddenly Reset:

SYMPTOMS
When you view the details of the objects in the Database window, you see that all forms, reports, macros, and modules have the same created and modified date. However, tables and queries have the actual date when they were created or modified.

CAUSE
This behavior occurs when you compact a database. Compacting a database resets the Created and Modified columns for all Access project items to the date and time when you compact the database. Access project items consist of all forms, reports, macros, and modules.

MS goes on to acknowledge that “this is a problem in Microsoft Access 2000.” It appears that this does not occur in Microsoft Access 2002. Another factor to take into account….

Reply | Quote

Testing some more, it appears the AccessObject DateModified property would be more accurate for modified objects. I made a design change to Form1 and got these results:

‘ DAO
? CurrentDB.Containers(“Forms”).Documents(“Form1”).DateCreated
12/28/2002 06:29:34 PM
? CurrentDB.Containers(“Forms”).Documents(“Form1”).LastUpdated
12/28/2002 06:36:34 PM

‘ ADO
? CurrentProject.AllForms.Item(“Form1”).DateCreated
11/20/2003 03:41:03 PM
? CurrentProject.AllForms.Item(“Form1”).DateModified
11/23/2003 04:04:32 PM

The DAO LastUpdated property does not reflect the modification. There’s an explanation for this behavior in MSKB Article 299554:

ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time

The article states in part: “Microsoft Access does not notify the Microsoft Jet database engine about the modification of Access-specific objects (forms, reports, macros, and modules); therefore, the DateUpdate column in the MSysObjects table is never updated to the correct date and time.” And: “Additionally, you may have noticed that the Database window displays the correct date and time for an object’s last modification. This is because Microsoft Access uses its own internal mechanism for storing the date created and date modified, independent of the Jet database engine. Unfortunately, Microsoft Access 2000 does not currently expose this information in its object model; therefore, there is no programmatic way to get to this information in Microsoft Access 2000.” But the AccessObject properties seem to reflect the same info as db window….

When I compacted db got same results as above (Access 2002), date/times did not change. Not sure if this is same in earlier versions of Access. In the db I’m using, almost all object dates reflect 11/20/2003, which was probably last time I compacted db when it was open in A2K rather than ACC 2002.

HTH

Reply | Quote

I don’t know if this clarifies anything, here is some sample results I got in a copy of Northwind:

‘ DAO object properties:
? CurrentDB.Containers(“Forms”).Documents(“Form1”).DateCreated
12/28/2002 06:29:34 PM
? CurrentDB.Containers(“Forms”).Documents(“Form1”).LastUpdated
12/28/2002 06:36:34 PM

From Help:
DateCreated, LastUpdated Properties (applies to DAO Document object)
DateCreated returns the date and time that an object was created, or the date and time a base table was created if the object is a table-type Recordset object (Microsoft Jet workspaces only).

LastUpdated returns the date and time of the most recent change made to an object, or to a base table if the object is a table-type Recordset object (Microsoft Jet workspaces only).

Using ADO AccessObject properties for same object, got different results:

? CurrentProject.AllForms.Item(“Form1”).DateCreated
11/20/2003 03:41:03 PM
? CurrentProject.AllForms.Item(“Form1”).DateModified
11/20/2003 03:41:03 PM

These date/times reflect same info that is displayed in the db window, which reflects date/time the db was last compacted.

From Help:
DateCreated Property (applies to: AccessObject Object)
Returns a Date indicating the date and time when the design of the specified object was last modified. Read-only.

DateModified Property (applies to: AccessObject Object)
Returns a Date indicating the date and time when the design of the specified object was last modified. Read-only.

Testing with a linked table, the DAO and ADO objects returned same results:

DAO:
? CurrentDB.Containers(“Tables”).Documents(“Orders”).DateCreated
11/19/2003 08:51:45 AM
? CurrentDB.Containers(“Tables”).Documents(“Orders”).LastUpdated
11/19/2003 08:51:45 AM

? CurrentDB.TableDefs(“Orders”).DateCreated
11/19/2003 08:51:45 AM
? CurrentDB.TableDefs(“Orders”).LastUpdated
11/19/2003 08:51:45 AM

ADO:
? CurrentData.AllTables.Item(“Orders”).DateCreated
11/19/2003 08:51:45 AM
? CurrentData.AllTables.Item(“Orders”).DateModified
11/19/2003 08:51:45 AM

Note in this case all date/times reflect same date/time as displayed in db window, which seems to be date/time the table’s link was last refreshed via Linked Table Manager.

Not sure what to conclude from all this, other than that the DAO Document object DateCreated & LastUpdated properties may be more accurate indicators of when the object was actually created/modified than the data displayed in db window.

HTH

Reply | Quote

Also see MSKB article ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time.

Reply | Quote

Hans,

Thanks. I always love it when I read “Microsoft acknowledges this is a problem”. No mention of when a fix will be ready, or even IF a fix will ever be done. Just a “yeah, we know”.

Reply | Quote

Mark,

I’m afraid that in this case, it won’t be fixed any more. Although DAO is still the easiest way to get at many aspects of a database, and the only way for some of them, Microsoft isn’t interested in updating it. Although I can understand why they had to move to an OLE document format, it seems to me it would only be a few extra lines of code to set the Last Updated date and time in MSysObjects.

Reply | Quote

Hans,

At a minimum, you think they’d say “We aren’t going to correct it”!

Upon doing some testing, I did discover that by right-clicking on an object in the DB window, then just hitting the spacebar in the “Description” property (and then saving it), that the LastUpdated date in msysobjects does get modified! At least it’s a workaround.

Reply | Quote

Hans,

At a minimum, you think they’d say “We aren’t going to correct it”!

Upon doing some testing, I did discover that by right-clicking on an object in the DB window, then just hitting the spacebar in the “Description” property (and then saving it), that the LastUpdated date in msysobjects does get modified! At least it’s a workaround.

Reply | Quote

Mark,

I’m afraid that in this case, it won’t be fixed any more. Although DAO is still the easiest way to get at many aspects of a database, and the only way for some of them, Microsoft isn’t interested in updating it. Although I can understand why they had to move to an OLE document format, it seems to me it would only be a few extra lines of code to set the Last Updated date and time in MSysObjects.

Reply | Quote

Hans,

Thanks. I always love it when I read “Microsoft acknowledges this is a problem”. No mention of when a fix will be ready, or even IF a fix will ever be done. Just a “yeah, we know”.

Reply | Quote

Also see MSKB article ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time.

Reply | Quote