Enumerating an Access Database 1

[fdcusa]

I've been charged with this task, and I am stumped. The task:

"Enumerate all objects in an Access Database.
List ALL objects and their details."

The objective is to have a table of every object, so that if one were to change a combo box somewhere, they can use the table to see where else that combo box is referenced throughout the database. For now, I am to just generated the table of references.

I found the CreateTableDefX() example using Northwind.mdb, and even seen references to enumeration in Access 2002 Dev Handbook (Enumerating Objects in a Collection, and Properties). But, I am still not sure where to even start...

This "project" isn't for a grade. I'm in a Software Support position for an Access/VB/SQL based application, and they say they are helping me learn how to do Programming (which I want), but it seems to me they have left out some pertinent information that I don't know and haven't found. Any assistance would be most appreciated.

I was a Programmer (QB and the earlier stuff) before Access/VB came out, and I recently came out of the caves....

 

[fdcusa]

More Info:

Some examples I was given is to identify in each row the Object type (Table/Query/Form/Report/Macro). Then identify the Object name (txtOrderID), Object type (Textbox), and the Object Parent (Orders_OrderDetails_subform).

If the object is a form or report, identify the recordsource.

I believe that's is all the notes I have...

I am very foggy...

 

[PHV]

Have a look here:

http://www.jrbarnett.freeserve.co.uk/mdbdoc.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Have you looked at the built-in documenter? You can field pages and pages of information about all objects in your database.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fdcusa]

Both suggestions look great for documentation, and very helpful no doubt, which I will use in the future. However, I beleive what they want is the same type information built in to (preferably) one table where they could query for multiple occurences of a specific object in other objects -- ie, identify all objects that would be affected by changing one object.

Forgive me if that sounds muddy...

 

[dhookom]

There is information at

http://www.access.hookom.net/Documenter/

showing how to query the results of the documenter. Keep in mind that when you run the documenter again, the records in the table will change. If you want to "save" the results, use the doc_tblObjects table as the source of a Make Table query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fdcusa]

[ Spent time away from PC to relax alitte, after all, it is the weekend... ]

dhookom, I did look at the Documenter on your site, and created the three queries (Select/CrossTab/MakeTable), and was very informative and useful. However, I think the guys want me to use the TableDef/QueryDef/Containers method instead.

Today I have done ALOT of reading... I notice there is more than one way to the get the result. Right now working on the method using TableDef/QueryDef/Containers, and do note the need to leave out system objects and deleted objects.

Ideally I need to have this completely before tonight is over, so I can present it in the morning. So, any additional advice would be most welcome while I am working it now. Thanks!

 

[fdcusa]

Well, for something seemingly so simple, it isn't for me... No luck... I got nowhere going by the CreatTableDefX / TableDefX samples. I looked at several examples using other methods, but I cannot do it using TableDef/QueryDef/Containers... I'm flustered, and going to bed...

 

[jrbarnett]

Have a look at the MDB Doc source code from

http://www.jrbarnett.freeserve.co.uk/mdbdoc.htm

Its not too difficult to adapt it to write information to a table - but bear in mind, you will have to exclude that information from the tables queried.

At the moment it doesn't include controls on forms/reports, but it could easily be added.

John

 

[fdcusa]

Hey everyone, I didn't give up... I managed to create the objects table for the first step, and my immediate boss was impressed. I found several ways I could have done this, including using the MSysObjects table and SQL, but then I wouldn't have learned anything.

With the current database, there are two issues:

1) I have a "Rebuild Object Inventory" button on the form which when selected, gives error that the object is already open or in use. I suppose because I am viewing the table in the form that I am attempted to re-build?

2) The Print button on the form. When clicked, I want the form to modify the Record Source of the report, according to the object(s) selected - either a specific object or all objects. What I have tried is:

Report.rptObjectsEnum.RecordSource = "SELECT ObjectName,ObjectType,DateCreated,LastUpdated FROM " & tdfName & " WHERE ObjectType" & strtemp & " Order by ObjectType,ObjectName"

I wanted to attach my project here for review, but don't see that option(?). Goto

http://jwhite.home.mindspring.com

and download DOEa01.zip.
Any tips/corrections to the design would be most welcome. I am sure I have "bumps" in the code...

Below I will copy an outline of what I (believe) I am trying to accomplish. I got the first step done - just getting a list of the primary objects, but am not sure where to start to get the other objects listed (those otherthan table/query/form/report/macro/module)...

If anyone has time to check it out, I would be most appreciative. I suppose I could use a documenter app to do this, but then I wouldn't learn anything!

John

DATABASE ENUMERATION PROJECT
--------------------------------------------------------------------------------

OBJECTIVES

Design a database that will record each Object Name in a database, and list
its Object Type, and Object Parent.

Provide function to list objects in database for the following types:

Table, Query, Form, Report, Macro, Module, or ALL

Provide function to query objects by Object Name. Return list of other
objects that reference the Object Name. For example:

I have an Object Name that I want to re-name. Where else is this
Object Name used in the database?

If I make changes to a form's XYZ control, what other areas will be
affected by the change?

I have an Object Name that I want to add to a form, query, or report.
Is it already in use, and where is it used?

TABLE DEFINITION

The table shall be generated listing every Object Name in the database and
identifying its Object Type, and Object Parent. The table shall have the
following fields:

1) AutoNumber

Auto-generated number when table is created. Is Primary Key.

2) ObjectName

Name of the object.

3) ObjectType

Table, Query, Form, Report, Macro, Module,
Label, Textbox, Option Group, Toggle Button, Option Button,
Check Box, Combo Box, List Box

4) ObjectParent

Name of the object that the ObjectName is within.

5) ObjectSource
If ObjectType is Query , record the SQL Statement.
If ObjectType is Form , record the Record Source.
If ObjectType is Report , record the Record Source.
If ObjectType is Macro , record the Macro Statement (?)

 

[fdcusa]

I figured out how to fix #2 on my list above! I read that you cannot update a report recordsource unless the report was open. In short, I created the SQL Select statement on-the-fly, using QueryDefs updating the query qryObjects, then called the report. Voila! I got it! I'm so proud of me!

Private Sub PrintObjectList_Click()
    Dim strSQL As String
    Dim strtemp As String
    Dim qdftemp As QueryDef
    Dim tdfName As String
    tdfName = "tblObjects"
    
    If Me!txtObject = "<ALL>" Then
        strtemp = "<> " & Chr(34) & Chr(34)
    Else
        strtemp = "= " & Chr(34) & Me!txtObject & Chr(34)
    End If
        
    strSQL = "SELECT ObjectName,ObjectType,DateCreated,LastUpdated " & _
             "FROM " & tdfName & " WHERE ObjectType" & strtemp & _
             " ORDER BY ObjectType,ObjectName;"
             
    Set qdftemp = CurrentDb.QueryDefs("qryObjects")
    qdftemp.SQL = strSQL
    
    strtemp = ""
    tdfName = ""
    strSQL = ""
    
    DoCmd.OpenReport "rptObjectsEnum", acViewPreview
    
End Sub

 

[fdcusa]

Just added a Preview button and a NoData event to save paper!

 Private Sub PrintObjectList_Click()
    
    On Error GoTo HandleErr
....
....  
    If Me!PrintPreview Then
        DoCmd.OpenReport "rptObjectsEnum", acViewPreview
    Else
        DoCmd.OpenReport "rptObjectsEnum"
    End If

ExitHere:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 2501: 'OpenReport action cancelled, no data, do nothing.
        Case Else: MsgBox Err.Number & ": " & Err.Description
    End Select
    Resume ExitHere
End Sub

... And in Report:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Sorry, no records matched your criteria!", _
           vbExclamation, "No Records to Print"
    Cancel = True
End Sub

To the average+ UA member this might seem trivial, but I'm pleased with myself!

 

[fdcusa]

Well, no one is replying....

Anyway, I figured out Problem #2 Rebuild Object Inventory...

Got to thinking... Why was I deleting the table then recreating it every time? Sooo, made more sense to just delete the records in the table, then add. CreateTable should be done only once... DoCmd.SetWarnings is set to False so I don't get that warning about deleting X number of records. With this, I no longer get the error of file in use.

    'Purge Objects Table of old records
    '
    DoCmd.SetWarnings False
    strSQL = "DELETE * FROM " & tdfName & ";"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

I've thought about including detection for hidden files (less MSys), which really should be counted. Is there an easy way to find all the Hidden files without going through the iteration of each object type?

The database at work that I am applying this to has 440 tables, 701 queries, 415 forms, 92 reports, 104 macros -- comes to about 1,761 objects give or take a few. Plenty of objects for me to work with! It is a LIMS application with BE/FE, currently Access/SQL Server. That doesn't count the Stored Procedures (BE SQL) and Modules (DLL). I know I can't see in the DLL, but if I had the permissions, couldn't I see the Stored Procedures?

Oh, yea, I also figured out how to get the Record Source of Forms and Reports, and the the SQL statement of Queries!

    ' QUERIES ----------------------------------------------------------
    For Each qdf In db.QueryDefs
        If Left$(qdf.Name, 1) <> "~" Then
            With rsT
                .AddNew
                ![ObjectName] = qdf.Name
                ![ObjectType] = "Query"
                ![ObjectSource] = qdf.SQL
                ![DateCreated] = qdf.DateCreated
                ![LastUpdated] = qdf.LastUpdated
                .Update
            End With
        End If
    Next qdf
    
    ' Forms ------------------------------------------------------------
        For Each aob In .AllForms
            With rsT
                .AddNew
                ![ObjectName] = aob.Name
                ![ObjectType] = "Form"
                ![ObjectSource] = Forms(aob.Name).RecordSource
                ![DateCreated] = aob.DateCreated
                ![LastUpdated] = aob.DateModified
                .Update
            End With
        Next aob

With the latest postings, I hope that I am making it clear I am not wanting 100% answers, but some clues here and there would be great!