How to capture last used date/time

  • Thread starter Thread starter K90267000 via AccessMonster.com
  • Start date Start date
K

K90267000 via AccessMonster.com

Hello all,
I am using access 2007.
How can I capture the date/time of the last usage of my database?
(either it was last open and closed, or it was last manipulated (record
change))
Any help is much appreciated.
Thanks.
Ken.
 
How can I capture the date/time of the last usage of my database?
(either it was last open and closed, or it was last manipulated (record
change))
Click to expand...

Access does not record this information. You'll need to create a table to
store the date/time, and write some (very much non-trivial!) code to write to
that table on every event that you want to track.

John W. Vinson [MVP]
 
Access does not record this information. You'll need to create a table to
store thedate/time, and write some (very much non-trivial!) code to write to
that table on every event that you want to track.
Click to expand...

When I look at an mdb file in Windows Explorer I see a dates for
'Modified' and 'Accessed' respective. Do you think these could be
used? (I really don't know.)

Jamie.

--
 
When I look at an mdb file in Windows Explorer I see a dates for
'Modified' and 'Accessed' respective. Do you think these could be
used? (I really don't know.)
Click to expand...

They're too hair-trigger - the Modified data changes if you open the database,
look at the screen, and close it. I've never found it useful.

John W. Vinson [MVP]
 
You can open a form (which can be hidden) when the database is opened and
run a piece of code to timestamp and save to a table when the database is
opened something like:

Public Sub AddDate()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From tblDatabaseDates;")

With rst
.AddNew
!DateOpened = Now
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

Similar code can be used in a close event of the form. If you add a
LastUpdated field to each table, the form which writes to that table can
fill the field in the BeforeUpdate event of the record. Access does not have
Triggers, so all events must be run in Code from forms or reports.
 
They're too hair-trigger - the Modified data changes if you open the database,
look at the screen, and close it. I've never found it useful.
Click to expand...

Thanks for the reply. I suspected that might be the case.

FWIW the 'forms' approach may have the opposite problem i.e. code not
triggered often enough if the mdb is accessed 'externally' -- which
reminds me, did you say in another thread you have a technique for
ensuring the mdb is only accessed via one application? Thanks again.

Jamie.

--
 
which
reminds me, did you say in another thread you have a technique for
ensuring the mdb is only accessed via one application?
Click to expand...

Not I, but if you hear of one let me know!

John W. Vinson [MVP]
 
Not I, but if you hear of one let me know!
Click to expand...

I don't know of one either :)

FWIW I'd recommend the OP considers an 'industrial strength' DBMS such
as SQL Server which not only has journaling etc but also DML and DDL
triggers to trap specific changes.

Jamie

--
 
FWIW I'd recommend the OP considers an 'industrial strength' DBMS such
as SQL Server which not only has journaling etc but also DML and DDL
triggers to trap specific changes.
Click to expand...

If they need to trap every change to any field in any table, I'd absolutely
agree. That would be a total monster to program in Access.

John W. Vinson [MVP]
 
Back
Top