Documenting / Managing Query Usage

[jholzman1]

A simple question about managing queries:

Is there a (simple) tool in Access, which I can use to document which queries are used in other queries or forms, etc. (In other words, I want to delete all queries I'm not using, but I'm afraid to rely on my memory to determine which ones are expendable.

 

[Norie]

You could use code like this for queries:

Sub UsesTable(TableName As String)

Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
    
    For Each qdf In db.QueryDefs
        If InStr(qdf.SQL, TableName) Then Debug.Print qdf.Name
    Next
    
End Sub

For forms it is a more complicated:

Function chkRecordSource(rsc As String) As String
' will return with the names of all the forms that
' use rsc as a RecordSource or if none do, an empty string

Dim ctr As Container
Dim db As Database
Dim frm As Form
Dim doc As Document

Set db = CurrentDb
Set ctr = db.Containers("Forms")

For Each doc In ctr.Documents

    DoCmd.OpenForm doc.Name, acDesign
    Set frm = Forms(doc.Name)
    If frm.RecordSource = rsc Then chkRecordSource = chkRecordSource & Chr(13) & frm.FormName
    DoCmd.Close acForm, frm.FormName
    
Next
    
End Function

You can adapt these for reports and to perform other functions, say just simply list all queries and tables.

You could also use them to create tables containing the information.

 

[jholzman1]

Norie:

Thanks so much -- that is great. Of course, here comes my next questions (which really gets right to the point: I'm quite ignorant when it comes to Access and VBA): Where exactly do I put that code you so graciously provided? Fiddling around, it looks like it belongs in a "Module," but I'm not at all sure. Can you help (again).

Again - thanks,
jh

 

[Norie]

Yes put it in a Module.

As I said you could use this type of Sub/Function to create tables.

Again that's a little more complicated.

If you want I could post an example of how to do this, but I don't have that code at hand right now.

To complicate matters further you could create files with the information, it just depends what you want.

BTW Do you understand how to use something like this?

For the UsesTable procedure you would need to add something like this:

Sub Test()

UsesTable "Table1"

End Sub

You would then run Test and in the Debug window of the module you should get a list of all the queries that use 'Table1'.

I hope you understand what I mean, I never knew anything about these sort of things a few years ago but learned how by trial and error.

 

[Piglett]

Hi

Norie, very interesting last post, to be able to store all definitions of a db within a table, within itself.

"
You can adapt these for reports and to perform other functions, say just simply list all queries and tables.

You could also use them to create tables containing the information.
"
This is one thing that I would like automated in a basic way, it TblName, FldName, FldType, Size, so that I could query this later on for documentation purposes. Also any objects to be detailed in the "Properties" table.

Also ability to visibly show the relationships window.

Have you ever taken this on as a project (or other users), as the built in documenter, although slick, is no good in a GMP/Manufacturing environment.

Piglett