Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Modified Date (All versions)

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Modified Date (All versions)

    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").D ateCreated
    12/28/2002 06:29:34 PM
    ? CurrentDB.Containers("Forms").Documents("Form1").L astUpdated
    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modified Date (All versions)


  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Modified Date (All versions)

    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").D ateCreated
    12/28/2002 06:29:34 PM
    ? CurrentDB.Containers("Forms").Documents("Form1").L astUpdated
    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

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Modified Date (All versions)

    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".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modified Date (All versions)

    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. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Modified Date (All versions)

    ? 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"!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Modified Date (All versions)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Modified Date (All versions)

    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....

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Modified Date (All versions)

    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).

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Modified Date (All versions)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Modified Date (All versions)

    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").DateModi fied
    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.

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Modified Date (All versions)

    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.

  14. #14
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Modified Date (All versions)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modified Date (All versions)

    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.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •