Tutorial :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.


Solution:1

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.


Solution:2

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.


Solution:3

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.


Solution:4

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.


Solution:5

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  


Solution:6

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »