Database properties (1 Viewer)

[Pat Hartman]

On the general tab of the database properties dialog is a property named "Modified" which logs the last time an object was saved to the db. Does anyone know how to reference this property in code?

 

[The_Doc_Man]

Dim dtUpdated as Date

dtUpdated = FileDateTime(CurrentDB.Name)

But I don't know if it will be updated immediately on opening the file. If so, then this approach is kind of useless for anything of any importance.

 

[Pat Hartman]

The_Doc_Man, thanks but I don't think that method would work since Access marks the file as updated every time you open it even if you don't change anything. I need to know when I last changed any objects.

I found another way to get a date and will use this method unless something better turns up:

Me.txtChangeDate = DMax("DateUpdate", "MSysObjects")

and I got as close as :
Debug.Print "msysdb = " & mydb.Containers!Databases.Documents!msysdb.LastUpdated

which is close but not quite right by using the containers collection.

 

[Tim K.]

I second The_Doc_Man's suggestion. The Modified property is from the FileDateTime().

The DateCreated and LastModified return the same value.

 

[WayneRyan]

Pat,

I made a simple form and populated the unbound text
boxes with the following:

' ******************************************
Private Sub Form_Open(Cancel As Integer)
Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "select Name, DateUpdate from MSysObjects " & _
"where Type = -32768 " & _
"order by DateUpdate desc"
Set rst = dbs.OpenRecordset(sql)
Me.FormsName = rst!Name
Me.FormsDate = rst!DateUpdate

Set dbs = CurrentDb
sql = "select Name, DateUpdate from MSysObjects " & _
"where Type = -32764 " & _
"order by DateUpdate desc"
Set rst = dbs.OpenRecordset(sql)
Me.ReportName = rst!Name
Me.ReportDate = rst!DateUpdate

Set dbs = CurrentDb
sql = "select Name, DateUpdate from MSysObjects " & _
"where Type = -32761 " & _
"order by DateUpdate desc"
Set rst = dbs.OpenRecordset(sql)
Me.ModuleName = rst!Name
Me.ModuleDate = rst!DateUpdate

Set dbs = CurrentDb
sql = "select Name, DateUpdate from MSysObjects " & _
"where Type = 1 and Name Not Like 'MSys*' " & _
"order by DateUpdate desc"
Set rst = dbs.OpenRecordset(sql)
Me.TableName = rst!Name
Me.TableDate = rst!DateUpdate

Set dbs = CurrentDb
sql = "select Name, DateUpdate from MSysObjects " & _
"where Type = 5 and Name Not Like '~*' " & _
"order by DateUpdate desc"
Set rst = dbs.OpenRecordset(sql)
Me.QueryName = rst!Name
Me.QueryDate = rst!DateUpdate

End Sub
' ******************************************

Wayne

 

[pdx_man]

MSDN shows FileDateTime as:

Returns a Date value that indicates the date and time when a file was created or last modified.

Here is some code to compare what I've seen here and gleened from the Help Files.

Sub ModInfo()
    Dim dbs As Database, cnt As Container
    Dim doc As Document
    Dim GetModInfo As String

    Set dbs = CurrentDb
    Set cnt = dbs.Containers!Databases
    Set doc = cnt.Documents!AccessLayout
    doc.Properties.Refresh
    GetModInfo = doc.Properties("LastUpdated")
    Debug.Print GetModInfo
    Debug.Print "msysdb = " & dbs.Containers!Databases.Documents!msysdb.LastUpdated & Chr(13)
    Debug.Print FileDateTime(dbs.Name)

End Sub

 

[Pat Hartman]

Thanks all. It looks like the "modified" date does not show the date of the last item modified and it does in fact coorespond with the file date that you get with FileDateTime(CurrentDB.Name). It doesn't look like the three items found by pdx_man quite do it either.

I think I'll change my DMax() to use a query that selects only the user objects from MSysObjects. The problem with the unqualified lookup that I am currently doing is if you open any object in design view, Access changes the modified date of AccessLayout which is an item in MSysObjects. This means that even if you don't actually update anything, the last update will change unless the AccessLayout item is excluded from the DMax().

Merry Christmas.