Mapping Database Object Usage... 1

[ahmun]

Hello All,

I'm wondering if there is a way to have Access 2000 generate a listing of where all the different objects in the database are used. (i.e. what form/vba/report is using a certain query)

Or should I develop better documentation and programming habits?

Thanks! Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[MichaelRed]

There is no intrinsic toool for this, however within some limits it is not all that difficult. Just itterate through the various collections and search for the various object names. List each occurance in a structure (table?). The derived uses are more of an issue.

Start with getting the names of all objects as the first field of the structure. When this is complete, you should have each Table, Query, Form, Report, Procedure (and possibly MACRO). For all except the procedures, it is simply an iteration of the collection. For Procedures, you need to get the individual procedure names from the various modules (I don't do or use MACROS, so will not mention them further). With the coplete list of object names, itterate through the collections again, opening each object and parseing through the open item for each of hte other named objects, and adding an entry to the structure. When complete, you shoud have the necessary information to buile a reasonable cross reference list.

In some instances, this approcah will be in error. This occurs, in my experience, where the sql property of a query def object is altered in code, and where un-named recordsets (querydefs) are generated in code.

There are third party tools (e.g. SpeedFerret) which purport to do a "COMPLETE" job of this, however I have not used it, so cannot vouch for it's capabillity.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Hi,
Here is a sample bit of code that will search all the tables and will print table and field names. This code should be placed in a module. As you probably know, the Debug.Print statement will put the results in the Immediate window (accessed via CTRL-G). If necessary, you can then highlight the code and paste into Word, then print.

Public Sub GetTableObjects()
Dim db As Database
Dim tbl As TableDef
Dim Fld As Field
Set db = CurrentDb
For Each tbl In db.TableDefs
Debug.Print "Table: " & tbl.Name & " ***"
For Each Fld In tbl.Fields
Debug.Print Fld.Name
Next Fld
Next tbl
End Sub

You can easily run this code from inside the Module object. Open up the module, place the cursor on the title line (Public Sub Get.....), then click the Run Macro button (arrow pointing to the right). HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

... and by placing the results in structures (table(s)?), you can have the start of the home-grown version of the cross-reference utility.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ahmun]

Here's a deeper look at what I'm trying to accomplish:

I've made some ad-hoc queries and reports and forms... without much care to document my progress. This was due to a deadline...

Now as I look back, I wanna clean up the database, and delete any queries I don't need or aren't being used, and rename queries to something more meaningful.

The nightmare is that some queries are used in textboxes as DLookups or DSums, and some are datasources of a form or report, some are used as an intermediate query in nested queries for complex querying.... u start to get the picture.

Any ideas how to (without having to cycle manually through each control on the form/reports) find where all my queries have been used, so I can clean things up?

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[MichaelRed]

... as previously noted ...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Michael,
You make it sound so easy, but I am having difficulty finding all the documentation for all the collections within each of the different objects, such as reports and queries. As Earnie alluded to, many of his reports use Dlookups, Dsum's, etc.
Have you considered creating a cross reference tool like this? I would be happy to be one of your first customers. In the meantime, this tool would be extremely useful to myself and others, and I will start experimenting with the reports collection. HTH,
Randy Smith
California Teachers Association

 

[ahmun]

To have a comprehensive tool to do this sounds like a programming job.. writing a parser and all that will read every control on every object... capturing the

.Value

, the

.Events

(all of them), the .etc.... properties in each control...

But for the meantime.. i think that's a good idea.. I'll have to at least hardcode a routine to capture all the values in the source property of each control.. and then do a string search in the vba code.. (hopefully Access 2000's help file will show me how to access different objects and control properties)

Thanks for the ideas, Mike, Randy. Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[randysmid]

Earnie,
Here is a code segment that will read the queries, and send the output to a text file.

Public Sub GetQueryObjects()
Dim db As Database
Dim qry As QueryDef
Dim Fld As Field
Set db = CurrentDb
Open "c:\TESTqueryList.txt" For Output As #1
For Each qry In db.QueryDefs
Print #1, qry.Name
For Each Fld In qry.Fields
Write #1, " - - " & Fld.Name & ", " & Fld.SourceTable
Next Fld
Next qry
Close #1

End Sub
HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

Randy,

Now replace the text file with a table addition and you have the beginning of the XRef, although it doe get more difficult (complex) as you delve into other objects. Even the 'field list' can become complicated when you start attempting to breakdown calculated fields (and again when the calculated field involves a function, and yet another level of again for UDFs ... ).

When the 'complete set of objects are completed, is is just a matter of some queries to generate the real cross reference. Although except for formal documentation package requirements, I have never really generated this (final and complete) crossreference listing. The information is (or can be) useful on larger projects, however it is a bit of a chore to use, as to get accurate *e.g. actually current) results you need to re-run the entire collection process -which can take quite a while on larger apps.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Michael,
I have gotten this far in the coding for the reports collection, but cannot seem to get past the report name (ao.Name) to find the controls on the report, and its corresponding recordsource. Any ideas?

Public Sub GetReportObjects()
Dim db As Database
Dim rpt As Report
Dim prop As Property
Dim ctrl As Control
Dim ao As AccessObject

Set db = CurrentDb

For Each ao In Application.CurrentProject.AllReports
Debug.Print "REPORT: " & ao.Name
'For Each ctrl In ao.????
' Debug.Print " ---> " & ctrl.Name
' add more code here when the controls are ID'd
'Next ctrl
Next ao HTH,
Randy Smith
California Teachers Association

 

[JeremyNYC]

I haven't read any of the code posted here, though I know these two know their stuff. I will say that I've got Rick Fisher's Find and Replace (like the Speed Ferret product Michael mentioned), and it has a feature that does this quite well. This is the one add-in product I've paid for in a bunch of years of developing Access databases, and I use it often enough to wonder how people do without one of these products. You can get it at

http://www.rickworld.com/products.html.

I don't know speed ferret, but I would assume it's quite good, too.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[MichaelRed]

randysmid

Here is a somewhat hacked version to at least get you to the controls collection of a form. The 'original' for this had A LOT of exception processing for a specific db project, and I have only run it briefly aginst an unbound form. DO NOT regard it as the 'proper' way to do everything, as (already noted), it was quickly hacked out of a weird (and quite specific) version, so even some simple things like declarations et al are woefully fubared, but the general approach works for both forms and reports.

The only real caution (for this SPECIFIC procedure) is to be SURE that all forms are closed before running it, as it will choke on any open ones. In production use, there should be exception processing for a form which could be run to the overall process (collect info from all object types). It would (also) be useful to have the process close all open objects EXCEPT the form used to collect the info.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

OOPS!!!!!!!!!

Public Function FormSw()

    'Michael Red    12/10/2002  Part of db Documentation

    Dim MyDb As DAO.Database
    Dim MyFrm As DAO.Recordset
    Dim MyFrx As Form
    Dim FldX As Integer
    Dim MyMtrxFrm As DAO.Recordset
    Dim MyFrmNm As String
    Dim F_Type As Integer
    Dim FrmX As Integer
    Dim EOW1 As Integer
    Dim EOW2 As Integer
    Dim BOW1 As Integer
    Dim BOW2 As Integer
    Dim Idx As Integer
    Dim NxtChr As String
    Dim SourceStuff As String
    Dim ThisWd As String
    Dim Subst1 As String
    Dim WD1 As String
    Dim CtrlSrc As String
    Dim CtrlNam As String
    Dim MyNewVar As String

    Set MyDb = CurrentDb
    Set MyMtrxFrm = MyDb.OpenRecordset("MyMtrxFrm")
    
    While (Not MyMtrxFrm.EOF)
        MyMtrxFrm.Delete
        MyMtrxFrm.MoveNext
    Wend

    For FrmX = 0 To MyDb.Containers(2).Documents.Count - 1
        MyFrmNm = MyDb.Containers(2).Documents(FrmX).Name

        DoCmd.OpenForm (MyFrmNm), acDesign, , acFormReadOnly, acHidden
        Set MyFrx = Forms(0)
        For FldX = 0 To MyFrx.Count - 1
            On Error GoTo SkipCtrl
            CtrlSrc = MyFrx(FldX).ControlSource
            CtrlNam = MyFrx(FldX).ControlName
            On Error Resume Next
            If ((MyFrx(FldX).Visible = False) And (MyFrx(FldX).Enabled = False)) Then
                GoTo SkipCtrl
            End If
            MyMtrxFrm.AddNew
            MyMtrxFrm!MyFrm = MyFrmNm
            MyNewVar = MyFrx.RecordSource
            EOW1 = InStr(MyNewVar, " ")
            If (EOW1 <> 0) Then
                WD1 = Left$(MyNewVar, EOW1 - 1)
             Else
                WD1 = MyNewVar
            End If

            If (StrComp(WD1, &quot;SELECT&quot;, 1) = 0) Then
                BOW2 = InStr(MyNewVar, &quot;FROM&quot;)
                Subst1 = Mid$(MyNewVar, EOW1 + 1, BOW2 - EOW1)
                ThisWd = &quot; &quot;
                SourceStuff = &quot;SQL from &quot;
                For Idx = 1 To Len(Subst1)
                    NxtChr = Mid$(Subst1, Idx, 1)
                    If (NxtChr$ <> &quot; &quot;) Then
                        ThisWd$ = ThisWd$ & NxtChr$
                     Else
                        If (InStr(ThisWd$, &quot;.*&quot;) <> 0) Then
                            SourceStuff$ = SourceStuff$ & &quot; &quot; & ThisWd$
                            ThisWd$ = &quot;&quot;
                         Else
                            ThisWd$ = &quot;&quot;
                        End If
                    End If
                Next Idx
             Else
                SourceStuff$ = MyFrx.RecordSource
            End If
            
            MyMtrxFrm!MyFrmSrc = Left$(SourceStuff$, 50)
            MyMtrxFrm!myfld = CtrlNam
            MyMtrxFrm!myfldtyp = CtrlSrc
            MyMtrxFrm.Update
SkipCtrl:
        Resume DoCtrl
DoCtrl:
        Next FldX
        
ClsFrm:
        Debug.Print MyFrmNm
        DoCmd.Close acForm, MyFrmNm

NoFrm:
    Next FrmX

    Debug.Print
    Debug.Print &quot;POW BOOM Done!!!!&quot;

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Michael,
What is "MyMtrxFrm"?
TIA. HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

it is a table (with the fields shown, plus a few which will be needed later [perhaps]). What I've been proposing all along, that the information collected be placed in a table, so additional parseing / querying of the objects can be done post collection, as opposed to simply sending them to some (unstructured) text repository.

I will admit that the 'naming' of the objects in this snippet are (at best) counterintuitive. I think this is one where I was working on a larger project where the 'lead' was in charge of the naming of almost EVERYTHING, so I can use the great neb. "they" as an excuse.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

WHEW!!!
I've been working with the code for the past hour or so. I had to read the code extremely carefully to find all the elements of the MyMtrxFrm table. This wasn't readily apparent from reading all your dim statements.

It now works wonderfully!!!

'gonna give you a star for it!!
HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

... one small step for 'randykind' ... ?

Reports can be handled the same way. Tables are actually easier. Queries become a lot more involved, if you break them down to individual fields, but can be handled at two levels (first is the simple querydef.sql) and the breakdown handled as a second tier. Modules will be the most difficult, as you need to look for numerous potential ways to reference other objects, so I would suggest that this one be the last element.

Since you are getting at least the basics down, I would also suggest that you stop for a bit and think this process through quite carefully. What do you want to do with the results -in detail-, and what structure will give you the capability to generate this 'output' without having to re-arrange the information. I geberally prefer to have a single table with the minimal -but complete- information on each item object. These, for me, are the source object (e.g. parent extended to the Container level, the complete parent tree (again to the container level), the name of the object, the recordsource of the immediate parent object, the controlsource of the object for forms and reports. For tables and queries you may want to consider this a bit differently and for modules / procedures it, perhaps, needs to be quite different. So, for my "single table" approach you may need to include fields which are not used for certain containers, or at least to have very careful deffinitions of hte fields.

Let me know how the project goes, I may be able to 'help' in some small way as you progress through this exercise, and I would certainly appreciate the opportunity to 'review' your soloutions along the way.

Oh, by the way, re-the 'obscure' naming used it the sample code, A FIRST step might be to re-organize this to a better / more structured process. You will, if you follow this to its logical conclusion, end up with a fair ammount of code and it would be quite beneficial to have it 'organized' in every manner = including the use of consistient (and sensible naming).


MichaelRed
m.red@att.net

Searching for employment in all the wrong places