Function that documents where queries are used?

[TessB]

Hey guys!
I have no idea if something like this exists or not. Today, I changed a query that drove the major portion of my form (bunch of subforms attached so I didn't want to have to create a new query....)

Now, I would like to know if there are any reports or queries that depend on this original query to see if they would be adversely affected.

Is there ANYWHERE I can run a function that would document which queries or reports use a particular query? This is a rather small database in comparison but I can't imagine making a small change like that in a HUGE database without such a tool.

Enlightenment please.
Thank you so very much.
Tess

 

[RichMorrison]

Tess,

Buy "Speed Ferret". It is a search utility for Access databases.

RichM

 

[Rich]

Option Compare Database
Option Explicit

Sub test1()
'To use: Import this module into your database
'Change the string below (waddayawant) to the string you want to search for
'then run this test procedure. Open the debug window to view the results.
Debug.Print WhereReferenced("waddayawant")
End Sub

Function WhereReferenced(strWhat As String) As String
' This code
' was written by Lyle Fairfield, October 1999
' and is CopyRighted.
' You may use the code under the following conditions:
' 1. This message is not removed.
' 2. The code is not substantially altered.
Dim cnt As Container, doc As Document
Dim intFile As Integer
Dim strFile As String
Dim strBuffer As String
Dim lngObject As Long
Dim StrReferenced As String
With DBEngine(0)(0)
For Each cnt In .Containers
With cnt
Select Case .Name
Case "Tables"
lngObject = acQuery
Case "Forms"
lngObject = acForm
Case "Reports"
lngObject = acReport
Case "Scripts"
lngObject = acMacro
Case "Modules"
lngObject = acModule
Case Else
lngObject = 255
End Select
If lngObject <> 255 Then
For Each doc In .Documents
With doc
strFile = .Name & ".Txt"
On Error Resume Next
Application.SaveAsText lngObject, .Name, strFile
If Error = 0 Then
FileLen (strFile)
strBuffer = String(FileLen(strFile), vbNullChar)
intFile = FreeFile
Open strFile For Binary As #intFile
Get #intFile, , strBuffer
If InStr(strBuffer, strWhat) <> 0 Then
WhereReferenced = WhereReferenced & .Name & vbCrLf
End If
Close #intFile
Kill strFile
End If
End With
Next doc
End If
End With
Next cnt
End With
Set doc = Nothing
Set cnt = Nothing
End Function

 

[Rich]

Only in that if you search for say Inv it will return everything with inv, invoice invalid etc., or it appears to, sadly I don't have time at the minute to test it extensively.