Solved

Dates objects are created or mofified...

Posted on 2014-03-25
4
134 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 39954544
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
ID: 39954575
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
ID: 39954587
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 37

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 39954626
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

742 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