How do I know which was the last time a table was updated

[SHA]

I want to know how can I get, with VBA or Visual Script, the last time a table was updated, Wich property returns me that value?

Thanks,
Salvador Hernandez

 

[Dirk Goldgar]

I want to know how can I get, with VBA or Visual Script, the last
time a table was updated, Wich property returns me that value?

Thanks,
Salvador Hernandez

If you want to know the last time any record in the table was added,
modified, or deleted, there is no property that gives that information.
The best you can do in that regard is either maintain a "LastModified"
date/time field in the table (but that doesn't help with deletions), or
else only allow the table to be updated by procedures that record audit
information in another table.

If instead you want to know when the table's *design* was last modified,
you can get that from a property of the DAO TableDef object; e.g.,

Dim db As DAO.Database

Set db = CurrentDb
Debug.Print db.TableDefs("Table1").LastUpdated
Set db = Nothing

 

[SHA]

Dirk,

I tried your idea with the following code, but it did not work:
-----------------------------------------
'This is VBScript code
Set MyDB = CreateObject("ADODB.Connection")
'The ODBC Connection is called Transmisions
MyDB.Open "Transmisions"
'I want to know the last time table TransFor was modified
LastTime = MyDB.Table("TransFor").LastModified
MsgBox(LastTime)
MyDB.Close

 

[Dirk Goldgar]

Dirk,

I tried your idea with the following code, but it did not work:
-----------------------------------------
'This is VBScript code
Set MyDB = CreateObject("ADODB.Connection")
'The ODBC Connection is called Transmisions
MyDB.Open "Transmisions"
'I want to know the last time table TransFor was modified
LastTime = MyDB.Table("TransFor").LastModified
MsgBox(LastTime)
MyDB.Close

As far as I know, the ADO Connectin object doesn't have a Table
collection. For this you'd need to use ADOX, and the Tables collection
of a Catalog object. Something like this:

Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = "Transmissions"
LastTime = cat.Tables("TransFor").DateModified
MsgBox LastTime
Set cat = Nothing

But I'm not really all that conversant with ADO and ADOX, so that may
not be exactly right.

 

[SHA]

Dirk,

Thanks for your help, I get an error message like this "Microsoft VBScript runtime error: 94, Invalid use of Null:'LastTime'", and this happens in the line with the instruction "MsgBox LastTime".

Salvador

 

[Dirk Goldgar]

Dirk,

Thanks for your help, I get an error message like this "Microsoft
VBScript runtime error: 94, Invalid use of Null:'LastTime'", and this
happens in the line with the instruction "MsgBox LastTime".

Salvador

I don't know what "Transmissions" is, in your code and configuration.
This works for me, to get the last-modifed date of a table named
"Table1" in a test .mdb file:

'----- start of code -----
Dim cat, LastTime

Set cat = CreateObject("ADOX.Catalog")

cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Documents and Settings\Dirk\My Documents\test.mdb;"

LastTime = cat.Tables("Table1").DateModified
MsgBox LastTime

Set cat = Nothing

'----- end of code -----

 

[SHA]

Thank you Dirk, it worked all right.

Salvador

If you want to know the last time any record in the table was added,
modified, or deleted, there is no property that gives that information.
The best you can do in that regard is either maintain a "LastModified"
date/time field in the table (but that doesn't help with deletions), or
else only allow the table to be updated by procedures that record audit
information in another table.

If instead you want to know when the table's *design* was last modified,
you can get that from a property of the DAO TableDef object; e.g.,

Dim db As DAO.Database

Set db = CurrentDb
Debug.Print db.TableDefs("Table1").LastUpdated
Set db = Nothing


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)