The Data Access Objects (DAO) LastUpdated property returns incorrect dates/times in Microsoft Access database

Article translations Article translations
Article ID: 299554 - View products that this article applies to.
This article was previously published under Q299554
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

SYMPTOMS

The Data Access Objects (DAO) LastUpdated property returns incorrect dates/times for Microsoft Access forms, reports, macros, and modules.

CAUSE

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. The same behavior is true for Microsoft Access 2007, which uses the Microsoft Access Database Engine.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The LastUpdated property provided by DAO returns the correct date/time for database engine objects (tables, queries, and relationships), but it only returns the creation date, not the last modified date for Microsoft Access specific-objects (forms, reports, macros, and modules).

Prior to Microsoft Access 2000, Microsoft Access used a Jet database (.mdb) file to store both database objects (tables, queries, and relationships) as well as the Microsoft Access-specific objects (forms, reports, macros, and modules). Microsoft Access stored its specific objects in Jet system tables, specifically the MSysObjects and MSysModules (or MSysModules2) tables. The DateUpdate column in the MSysObjects table is responsible for storing an object's last modification date and is the column that the DAO LastUpdated property uses to return an object's last modified date. When a user modified and saved an object in earlier versions, Microsoft Access notified the Jet database engine that the object had been modified, and Jet updated the DateUpdate column to the current date and time.

Microsoft Access 2000 introduced Access project (.adp) files, which have the capability of connecting directly to Microsoft SQL Server databases without using the Jet database engine. Because ADP files do not use the Jet database engine (or the Access Database Engine), Access could not use the same storage format previously used in Jet databases for Access-specific objects. It had to store them in a new OLE doc storage format that is accessible in both Jet databases and Access projects. When the user initially creates a new Microsoft Access specific-object, such as a form), the database engine still enters the current date and time into the DateCreate and DateUpdate columns in the MSysObjects table. However, when the user modifies and saves the object, Microsoft Access does not notify the database engine; therefore, the DateUpdate column always stays the same.

Additionally, you may have noticed that the Database window (in versions prior to Access 2007) and the Navigation Pane in Access 2007 or Access 2010 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 database engine. Unfortunately, Microsoft Access does not expose this information in its object model; therefore, there is no programmatic way to get to this information in Microsoft Access.

Steps to Reproduce the Behavior

  1. Start Microsoft Access, and then create a new, blank database.
  2. Create a new, blank form in Design view.
  3. Add a text box to the form.
  4. Save the form as frmLastUpdated, and then close it.
  5. In Access 2003 and earlier versions of Access, select the View menu and click Details. In Access 2007 or Access 2010, right-click on the Category header in the Navigation Pane on the left and click View By Details. This displays the Modified and Created dates for each object.

    Note that both the Modified and Created columns for the frmLastUpdated form are set to the same date and time.
  6. Press CTRL+G to open the Immediate window in Visual Basic Editor. (In Access 2007 or Access 2010, you must first enable the content or the database must be in a trusted location.)
  7. Type the following line into the Immediate window, and then press ENTER:

    ?CurrentDb.Containers("Forms").Documents("frmLastUpdated").LastUpdated
    						
    Note that the date and time returned corresponds to the Modified and Created columns in the Database window.
  8. Press ALT+F11 to switch back to Microsoft Access.
  9. Open the frmLastUpdated form in Design view.
  10. Add a second text box to the form, and then save and close it.

    Note that the Modified column in the Database window contains an updated date and time for the form.
  11. Repeat steps 7 and 8.
Note The LastUpdated property still returns the original date and time instead of the new date and time displayed in the Modified column in the Database window.

Properties

Article ID: 299554 - Last Review: April 16, 2010 - Revision: 3.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 2002 Standard Edition
  • Microsoft Office Access 2003
  • Microsoft Office Access 2007
  • Microsoft Access 2010
Keywords: 
kbbug kbnofix KB299554

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com