Results 1 to 4 of 4
  1. #1
    Carbontrader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    12

    Print a table's "LastUpdated" metadata in a report

    Is there a quick-and-dirty way to extract the LastUpdated metadata property from an underlying table and stick in into a report?
    Attached Thumbnails Attached Thumbnails Capture3.JPG  

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The info is stored in the hidden system table MSysObjects in a field called DateUpdate
    So use a DLookup in that control

    Code:
    =Dlookup("DateUpdate","MSysObjects","Name='YourTableName'")
    That assumes you only have objects with unique names.
    If you also have e.g. a form with the same name, Access may get confused.
    In that case you also need to use the Type and Flags fields
    Type=1 for local tables, Flags=0 for non system tables

    The result will include the date and time. Format as short date if you don't want the time part
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Access does not track when data was last edited.

    The LastUpdated property is the last date the table structure was modified.

    Another way to get that date in VBA:

    CurrentDb.TableDefs("YourTableName").Properties("L astUpdated")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks June for the correction - that will teach me to answer when I'd just woken up!
    You are quite correct in stating that these give the same information

    Code:
    Dlookup("DateUpdate","MSysObjects","Name='YourTableName'")
    Code:
    CurrentDb.TableDefs("YourTableName").Properties("LastUpdated")
    In each case, the result is when the table structure was last modified
    This is also what is shown in the navigation pane

    If you want the date/time the data was last modified, you need to use a timestamp field created for that purpose:
    1. Add a field called DateModified to the table design with date/time datatype
    2. Add the field to the form used to edit data - format as general date if you want to see it - but it can be hidden
    3. Add the following code to your form
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
        Me.DateModified = Now()
    
    End Sub
    4. Add the DateModified field to your report
    Last edited by isladogs; 03-05-2018 at 06:42 AM. Reason: Added info on use of timestamp field
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2016, 03:12 AM
  2. Replies: 2
    Last Post: 04-12-2016, 12:58 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 10
    Last Post: 09-03-2015, 01:31 PM
  5. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums