How to Get Last Modified Date/Time for a Table

[ssh]

Hi, there,
I am trying to have a procedure get for me the date/time for when a table was last modified. I tried looking around on the web and on this forum but wasn't quite able to find what I was looking for.

The closest thing I found was the following code, which has the same idea, except it shows when a FILE or FOLDER was last modified. Any ideas on what I need to do to get the same info for a TABLE? Thanks!!

Sub ShowFileAccessInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(filespec) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub

 

[SydneyGeek]

If you want to track modifications to records and tables, you need additional fields. I usually have at least two: CreateDate and ModDate.

Then, in a form where the data is edited, you need to go to the BeforeUpdate event of the form and insert this code:

If Me.NewRecord Then
   CreateDate = Now()
End If
ModDate = Now()

Whenever a record is created or edited, it is timestamped.
You can then run a Top 1 query on the table, sorting descending on ModDate, to get the timestamp for the last edit on the table.

Denis

 

[wongm003]

You could try...

Public Function date_created(strTblName) As Variant
On Error Resume Next

    date_created = Null
    date_created = CurrentDb.TableDefs(strTblName).DateCreated

End Function

Public Function date_modified(strTblName) As Variant
On Error Resume Next

    date_modified = Null
    date_modified = CurrentDb.TableDefs(strTblName).LastUpdated

End Function

 

[ssh]

Thank you, wongm003 and Denis. Both methods work great!