Dates objects are created or mofified...

I need a routine to run in an access database to list objects created along with their created and modified dates.

Question: Do you have a routine like this or can you put together if not very involved?

Thank you.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
aikimarkConnect With a Mentor Commented:
This will pick up tables, queries, forms, and reports
Public Sub IterateAllObjects()
    Dim rs As Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("Select name, DateCreate, DateUpdate from msysobjects where flags=0 and type in (1,5,-32768,-32764)")
    Do Until rs.EOF
        Debug.Print rs!Name, rs!datecreate, rs!dateupdate
        rs.MoveNext
    Loop
End Sub

Open in new window

Note: You will get the system tables if you remove the flags=0 condition in the Where clause.
0
 
aikimarkCommented:
If you just need that information about tables and queries, you can use this:
Public Sub IterateTablesAndQueries()
    Dim td As TableDef
    Dim qd As QueryDef
    For Each td In DBEngine(0)(0).TableDefs
        Debug.Print td.Name, td.DateCreated, td.LastUpdated
    Next
    For Each qd In DBEngine(0)(0).QueryDefs
        Debug.Print qd.Name, qd.DateCreated, qd.LastUpdated
    Next
End Sub

Open in new window

0
 
Dale FyeConnect With a Mentor Commented:
which objects?  You can use something like:

SELECT [TYPE],[Name], [Owner], DateCreated, DateUpdated
FROM mSysObjects

But that will give you all objects.  To get more specific, you would need to use the [Type] field to restrict that to the specific types of objects.

Tables: 1 (local), 6 (Linked)
Forms: -32768
Reports: -32764
Queries: 5
Modules: -32761
0
 
PatHartmanConnect With a Mentor Commented:
There is no reliable method since Access changes the create and modified dates of some objects whenever you compact.  The create date usually survives but who among us has never had to rebuild a database due to corruption - there goes your create dates.

Do some testing so you understand when Access will change these dates on you.
0
All Courses

From novice to tech pro — start learning today.