Using VBA to find date modified of a table

  • Thread starter Thread starter Doug F.
  • Start date Start date
How can I find the date modified of an Access table.
Thanks,
Doug
Click to expand...

You can't, since Access does not store that information.

You can of course make arrangements to store it yourself, in that table or in
a separate log table.
 
Thanks John.

John W. Vinson said:
You can't, since Access does not store that information.

You can of course make arrangements to store it yourself, in that table or in
a separate log table.
Click to expand...
 
Thx.

John W. Vinson said:
You can't, since Access does not store that information.

You can of course make arrangements to store it yourself, in that table or in
a separate log table.
Click to expand...
 
But I can see a modified date and time when in the database window for tables
and click 'Details'?
 
CurrentDb.TableDefs("tableName").Properties("LastUpdated").Value



You may get an error if the property does not exist.


Vanderghast, Access MVP
 
CurrentDb.TableDefs("tableName").Properties("LastUpdated").Value
Click to expand...

I should just try it, Michel, but does this record the last date that the
*data in the table* was updated, or the last date that the *structure* of the
table was updated? And how does this work for a linked table?
 
John W. Vinson said:
I should just try it, Michel, but does this record the last date that the
*data in the table* was updated, or the last date that the *structure* of
the
table was updated? And how does this work for a linked table?
Click to expand...

AFAIK, it's the last date that the structure of the table was updated.
 
AFAIK, it's the last date that the structure of the table was
updated.
Click to expand...

But for linked tables, it's not for the underlying table, but for
the table link. You could run it on the back end table:

DBEngine.OpenDatabase(Mid(CurrentDB.TableDefs("TableName").Connect,11
)).tableDefs("Inventory").LastUpdated

In code, you'd likely do that with:

Dim strDatabase As String
Dim db As DAO.Database

strDatabase = Mid(CurrentDB.TableDefs("TableName").Connect,11)
Set db = DBEngine.OpenDatabase(strDatabase)
Debug.print db.TableDefs("TableName").LastUpdated

db.Close
Set db = Nothing

And you could make that a function, of course:

Public Function GetBackEndTableProperty(strTableName As String, _
strProperty As String) As Variant
Dim strDatabase As String
Dim db As DAO.Database

strDatabase = Mid(CurrentDB.TableDefs(strTableName).Connect,11)
Set db = DBEngine.OpenDatabase(strDatabase)
GetBackEndTableProperty = db.TableDefs(strTableName)(strProperty)

db.Close
Set db = Nothing
End Function

Of course, you'd want to put in error handling in case the property
doesn't exist, but that's the basic structure.
 
Thank you, Michel I believe.

vanderghast said:
CurrentDb.TableDefs("tableName").Properties("LastUpdated").Value



You may get an error if the property does not exist.


Vanderghast, Access MVP
Click to expand...
 
Michel's code worked and it's the last date the structure changed. This
happens to be ok for my purposes as the table is created by a make-table
query and I'm checking whether the user has run the query.
Thanks.
 
The last date the STRUCTURE changed, indeed (from my experimentation), not
for a data change.


For linked tables, if you refresh the link from the Linked Table Manager,
the Date Modified *AND* the Date Created properties both change (may need
a refresh of the Access Database window, though, to SEE it, such as
switching into Queries, then back in Tables, to "see" the change, or to use
CurrentDb, not a cached value of CurrentDb, through VBA). If you change the
structure source table, the Modified Date property is NOT propagated to the
linked table having that table as source... (as it would be quite hard to
realize such propagation, at least as I see it). :-)


I have not used much these properties, though.


Vanderghast, Access MVP
 
Back
Top