I need to search database code for instances of text within forms, Reports or public modules. I am attempting to find table and field names in code which may be referenced in SQL queries, dlookups etc. built into the code.
I have a nice function which looks through SQL statements in queries and searches data sources for controls on forms and reports but I am missing the background code.
[blue]
I am trying to get at the AfterUpdates, Before Updates, lostfocus and similar events along with anything in the public modules. [/blue]
[red]
I need to make a tool I can use to be confident I can locate everywhere a string is referenced. This will be handy when I have to change a name or delete a field or table.[/red]
This is a bit of the code I use for looking through reports to illustrate what I am attempting to accomplish.
This searches the source SQL for all controls on a report looking for any occurrence of a prompted sting. If any are found the function then writes where the string was located to a table I open earlier in the code. This works GREAT and fast.
How do I loop through all the Code??
This is the entire code for reference if someone finds this on a search, perhaps it could be usefull.
I have a nice function which looks through SQL statements in queries and searches data sources for controls on forms and reports but I am missing the background code.
[blue]
I am trying to get at the AfterUpdates, Before Updates, lostfocus and similar events along with anything in the public modules. [/blue]
[red]
I need to make a tool I can use to be confident I can locate everywhere a string is referenced. This will be handy when I have to change a name or delete a field or table.[/red]
This is a bit of the code I use for looking through reports to illustrate what I am attempting to accomplish.
This searches the source SQL for all controls on a report looking for any occurrence of a prompted sting. If any are found the function then writes where the string was located to a table I open earlier in the code. This works GREAT and fast.
Code:
For Each ctl In rpt.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox
If InStr(1, ctl.RowSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!ReportName = rpt.Name
!ControlName = ctl.Name
!SQL = ctl.RowSource
.Update
End With
End If
Case acTextBox
If InStr(1, ctl.ControlSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!ReportName = rpt.Name
!ControlName = ctl.Name
!SQL = ctl.ControlSource
.Update
End With
End If
End Select
Next ctlHow do I loop through all the Code??
This is the entire code for reference if someone finds this on a search, perhaps it could be usefull.
Code:
Sub SearchSQL()
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rec As Recordset
Dim frm As Form
Dim rpt As Report
Dim obj As AccessObject
Dim ctl As Control
Dim strSQL As String
Dim strSearch As String
On Error GoTo ErrHandler
strSearch = InputBox("Enter the search string", "Search ALL SQL")
If strSearch <> "" Then
DoCmd.Hourglass True
Application.Echo False
' create a table to hold the list
Set tdf = CurrentDb.CreateTableDef("ztblSQLSearch")
With tdf
.Fields.Append .CreateField("SearchString", dbText)
.Fields.Append .CreateField("DateCreated", dbDate)
.Fields.Append .CreateField("FormName", dbText)
.Fields.Append .CreateField("ReportName", dbText)
.Fields.Append .CreateField("ControlName", dbText)
.Fields.Append .CreateField("QueryName", dbText)
.Fields.Append .CreateField("SQL", dbMemo)
End With
CurrentDb.TableDefs.Append tdf
Set rec = CurrentDb.OpenRecordset("ztblSQLSearch", dbOpenDynaset)
' List all queries
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each qdf In CurrentDb.QueryDefs
'exclude all temporary queries
If Left(qdf.Name, 1) <> "~" Then
'check for the search string
If InStr(1, qdf.SQL, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = qdf.DateCreated
!QueryName = qdf.Name
!SQL = qdf.SQL
.Update
End With
End If
End If
Next qdf
' List all forms
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each obj In Application.CurrentProject.AllForms
' check for the search string
If Not obj.IsLoaded Then
DoCmd.OpenForm obj.Name, acDesign
Set frm = Application.Screen.ActiveForm
Else
Set frm = Forms(obj.Name)
End If
' Debug.Print frm.Name
Debug.Print frm.Name & " " & frm.RecordSource
If InStr(1, frm.RecordSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!FormName = frm.Name
!SQL = frm.RecordSource
.Update
End With
End If
' check each combobox and listbox
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox
If InStr(1, ctl.RowSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!FormName = frm.Name
!ControlName = ctl.Name
!SQL = ctl.RowSource
.Update
End With
End If
Case acTextBox
If InStr(1, ctl.ControlSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!FormName = frm.Name
!ControlName = ctl.Name
!SQL = ctl.ControlSource
.Update
End With
End If
End Select
Next ctl
DoCmd.Close acForm, frm.Name, acSaveNo
Next obj
' List all reports
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each obj In Application.CurrentProject.AllReports
' check for the search string
If Not obj.IsLoaded Then
DoCmd.OpenReport obj.Name, acDesign
Set rpt = Application.Screen.ActiveReport
Else
Set rpt = Reports(obj.Name)
End If
' Debug.Print rpt.Name
' Debug.Print rpt.RecordSource
If InStr(1, rpt.RecordSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!ReportName = rpt.Name
!SQL = rpt.RecordSource
.Update
End With
End If
' check each combobox,listbox or textBox
For Each ctl In rpt.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox
If InStr(1, ctl.RowSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!ReportName = rpt.Name
!ControlName = ctl.Name
!SQL = ctl.RowSource
.Update
End With
End If
Case acTextBox
If InStr(1, ctl.ControlSource, strSearch) > 0 Then
With rec
.AddNew
!searchString = strSearch
!DateCreated = obj.DateCreated
!ReportName = rpt.Name
!ControlName = ctl.Name
!SQL = ctl.ControlSource
.Update
End With
End If
End Select
Next ctl
DoCmd.Close acReport, rpt.Name, acSaveNo
Next obj
End If
DoCmd.OpenTable "ztblSQLSearch"
ExitHere:
On Error Resume Next
rec.Close
Set rec = Nothing
Set qdf = Nothing
Set tdf = Nothing
Set obj = Nothing
Set frm = Nothing
Set rpt = Nothing
DoCmd.Hourglass False
Application.Echo True
Exit Sub
ErrHandler:
Select Case Err.Number
Case 3010
On Error Resume Next
CurrentDb.TableDefs.Delete "ztblSQLSearch"
Err.Clear
Resume
Case Else
MsgBox Err.Number & ": " & Err.Description
Err.Clear
GoTo ExitHere
End Select
End Sub
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
