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

"SHA" <(E-Mail Removed)> wrote in message
news:B6714940-05D3-42EB-B852-(E-Mail Removed)
> 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


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

(please reply to the newsgroup)

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
-----------------------------------------
What am I doing wrong?

Thanks,
Salvador Hernandez
"Dirk Goldgar" wrote:

> "SHA" <(E-Mail Removed)> wrote in message
> news:B6714940-05D3-42EB-B852-(E-Mail Removed)
> > 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
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"SHA" <(E-Mail Removed)> wrote in message
news:B6A7D3B0-956D-4ECD-AAA9-(E-Mail Removed)
> 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
> -----------------------------------------
> What am I doing wrong?

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.

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

(please reply to the newsgroup)

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" wrote:

> "SHA" <(E-Mail Removed)> wrote in message
> news:B6A7D3B0-956D-4ECD-AAA9-(E-Mail Removed)
> > 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
> > -----------------------------------------
> > What am I doing wrong?
>
> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"SHA" <(E-Mail Removed)> wrote in message
news:39F93336-61DD-4E5A-89AF-(E-Mail Removed)
> 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 -----

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

(please reply to the newsgroup)