Solved

Dates objects are created or mofified...

Posted on 2014-03-25
4
131 Views
Last Modified: 2014-04-11
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.
0
Comment
Question by:Mike Eghtebas
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
Comment Utility
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now