Find all uses of a query in an access application

Question

I made a standalone Access 2003 app with a bunch of forms that is beginning to get unruly. I'm trying to clean it up a bit and I'd like to do things such as find all uses of a query so that I can make some global updates. I can use ctrl-f to find uses of a query in code but my question is: Is there an easy way to search the entire app to find where queries are used in the control's properties window such as when they are directly bound to a control?

It would be nice to programatically dump all properties of all forms to text files.

Answer 1

There's nothing built in, but you should be able to get the functionality by writing some code that iterates through the CurrentProject.AllForms collection to loop through all forms. For each form, check the properties that might have the query in them and make a list.

Answer 2

Forms can be saved to text files using SaveAsText:

Sub FormToText()
Dim frm
For Each frm In CurrentProject.AllForms
    Application.SaveAsText acForm, frm.Name, "c:\docs\" & frm.Name & ".txt"
Next
End Sub

You can get a great deal of information from system tables MsysObjects and MsysQueries.

Answer 3

Right-click on the query in the database window and select "Object Dependencies" This should give you the list of forms that host it as a sub-form.

This only works if you have Track Name Autocorrect Info turned on which is the default. I personally turn this off as it bloats the db.

Answer 4

Like DJ writes: this is the way to go.It works pretty good, it detects all queries, even the ones that are used in comboboxes etc. Although I don't think it will find queries that are used in code.

And if you change a query (the name for instance) all references to this query will be updated as well.

Answer 5

As a long-term user of Access who makes a living with it, I don't recommend Name AutoCorrect. For these kinds of things I use a search and replace utility. I've been a long-time owner of Black Moshannon's Speed Ferret, but it's been pretty problematic since the release of A2003, since it doesn't natively support more recent versions. Another alternative that I've not really used but that others recommend is Rick Fisher's Find and Replace.

Answer 6

I know this is a little old, but it looked like an interesting challenge. If you paste this into a module and run it, it'll produce a text file with the RecordSource for every form and the RowSource for every ComboBox or ListBox with a RowSourceType of "Table/Query". If I'm remembering correctly, that should get you every property where a query could be used. If I'm not remembering correctly, you can tweak the code to grab the others or to change the format of the output.

If you wanted to dump all properties of all forms, you could do a for each loop in the properties collection of the form and write that to a file. The issue there is that forms have certain properties, like PrtMip and PrtDevName, that are structures and therefore break the write or writeline methods, so if you were going to try to write those to a file you'd have to do some special handling first. Also, I believe the bookmark property can be problematic as well.

Sub ListProperties()
    Dim frm As Object
    Dim ctl As Control

    Dim fs As Object
    Dim file As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set file = fs.CreateTextFile("C:\FormProps.txt", True)

    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acNormal, , , , acHidden
    Next frm

    For Each frm In Forms
        file.writeline (frm.Name)
        file.writeline (String(Len(frm.Name), "-"))
        file.writeline "RecordSource" & Chr(9) & frm.Properties("RecordSource")

        For Each ctl In frm.Controls
            With ctl
                Select Case .ControlType
                    Case acComboBox, acListBox
                        .SetFocus
                        If .RowSourceType Like "Table/Query" Then
                            file.writeline Chr(9) & .Name & Chr(9) & "RowSource" & Chr(9) & .RowSource
                        End If
                End Select
            End With
        Next ctl

        file.writeline
    Next frm

    For Each frm In Forms
        DoCmd.Close acForm, frm.Name
    Next frm

End Sub