Searching Access

Peter Bryant

You can buy your tools, or you can build them yourself. Here's how an add-in was developed to search the objects, modules, controls, and properties of an Access 2.0 database.

Doctors swear to abide by the Hippocratic oath, which begins "First, do no harm . . .". Having inherited a large number of databases, and being charged with the maintenance of these applications, I was faced with the problem of ensuring that any changes I made wouldn't have any adverse effects on those databases. In order to do that, I needed some way to see what effect any change I made would have. Among other things, I needed a way to search for the name of any object (table, field, variable, and so on) that I was working with in order to see where it was used.

While Access allows you to search for strings in data and modules, it has no way to search for a string in forms, reports, or the many properties that are attached to Access's objects. Although I could have bought a third-party utility, Speed Ferret (reviewed in the Smart Access August 1996 issue), I thought developing a similar tool would be an opportunity to gain a better technical understanding of Access's objects.

The resulting add-in (see Figure 1) searches the Table/Query definitions, Forms, Reports, Macros, Modules, Import/Export specifications, and Table relationships for any string. When the search string is found, details of the Object and Control are saved to a table (see Table 1). Once the database has been searched, a report based on the resulting records is displayed by the Add-in.

Figure 1: Add-in dialog form

Table 1. The search report table.

Getting the utility to work consisted of figuring out how to handle each object type that I wanted to search.

The first area I tackled was the table definitions. Since the table names are held in the Name Property of the TableDefs Collection, they can be processed using a For...Next loop ranging from 0 to the TableDefs Count minus 1. This function does just that:

Sub TableNames(strSearch as String)

Dim dbCurr As Database
Dim dbCode As Database
Dim rsOutput As Recordset
Dim strName As String
Dim I As Integer

Set dbCurr = CurrentDb()
Set dbCode = CodeDb()
Set rsOutput =  _
   dbCode.openRecordSet("Usys_Rat_Objects")

For i = 0 To dbCurr.TableDefs.Count - 1
    strName = dbCurr.TableDefs(i).Name

    If Instr(intName, strSearch) Then
        rsOutput.AddNew
            rsOutput("ParentName") = objName
            rsOutput("ParentObj") = "Table Name"
        rsOutput.Update
    End If
Next

rsOutput.Close

End Sub

Because this function also processes the System-level tables held in the database, it was necessary to check for these objects and ignore them. To do this, I turned to one of the libraries that are distributed with Access itself, namely wzlib.mda. I found that this library contains a number of very useful functions. The FIsSysObj() function is a good example. Pass it the A_TABLE Access constant, the Table name, and the TableDefs.Attributes property and the function will return True if the table is a system object and False if it isn't. I added the following condition to my table-searching function:

If (Not FIsSysObj(A_TABLE, strName, _
    (db.TableDefs(i).Attributes))) Then
   'Now perform the search
End If

Once the table names were processed, the tables' field names needed to be checked. These are held as the Name Property of the Fields() Collection for the table they belong to and are used in a routine very similar to the one that processed the Table names.

Query names were tackled in the same way as the table names, but they used the Name Property of the QueryDefs() collection. In order to search the query itself, I had to use the SQL property of the QueryDef object. Although this property contains Carriage Returns, Chr(13), Nulls, and Chr(0), these characters can be treated as other characters in the string. Once a query was found to contain the string I was searching for, though, the Carriage Returns and Nulls had to be removed so that the SQL statement could be displayed on one line in the report.

Forms and Reports had to be opened in design mode to access the definition properties for each control. However, I found that the runtime properties, such as the Combo Box ItemData() property, were also available at design time and would cause errors when searched.

To avoid searching the runtime properties, I created a table to hold the names of the properties that could be searched. I then searched that table for each Property Name for each Control. When I found a match, I checked the Property value to see if it contained the search string or the [Event Procedure] statement (I ignored Event Procedures until the Code Behind Forms section of my program). The table containing the property names to be searched was held in the add-in database, so I used the CodeDb() function to get a reference to it (see Listing 1).

Listing 1. Searching reports and forms.

Sub FormProperties(strSearch As String_
objName As String)

Dim dbCurr As Database
Dim dbCode As Database
Dim rsProp As RecordSet
Dim rsOutput As RecordSet
Dim frm As Form
Dim i As Integer
Dim j As Integer

Set dbCode = CodeDb()
Set dbCurr = CurrentDb()
Set rsProp = _
  dbCurr.OpenRecordset("USys_Rat_Properties")
Set rsOutput = dbCode.OpenRecordSet_
("Usys_Rat_Objects")

DoCmd OpenForm objName, A_DESIGN

Set frm = Forms(objName)

For i = 0 To frm.Properties.Count - 1
  rsProp.MoveFirst
  rsProp.Index = "PrimaryKey"
  rsProp.Seek "=", frm.Properties(i).Name

  If Not rsProp.NoMatch Then
    If InStr(frm.Properties(i), _
"[Event Procedure]") = 0 Then
      If InStr(frm.Properties(i), strSearch) <> 0 Then
        rsOutput.AddNew
        rsOutput("ParentName") = objName
        rsOutput("ParentObj") = "Form Property"
        rsOutput("ProcName") = frm.Properties(i).Name
        rsOutput ("Line") = frm.Properties(i)
        rsOutput.Update
      End If
    End If
  End If
Next

For i = 0 To frm.Count - 1
  For j = 0 To frm(i).Properties.Count - 1
    rsProp.MoveFirst
    rsProp.Index = "PrimaryKey"
    rsProp.Seek "=", frm(i).Properties(j).Name
    If Not rsProp.NoMatch Then
      If InStr(frm(i).Properties(j), strSearch) <> 0 _
      Then
          rsOutput.AddNew
          rsOutput("ParentName") = objName
          rsOutput("ParentObj") = "Form Control"
          rsOutput("ProcName") = _
             frm(i).Properties(1) & "." & _
           frm(i).Properties(j).Name
          rsOutput("Line") = frm(i).Properties(j)
          rsOutput.Update
        End If
    End If
  Next
Next 

rsProp.Close
rsOutput.Close

End Sub

The next problem with the Forms and Reports was how to gain access to the CBF. I resolved this by exporting the underlying module using the OutputTo command like this (after setting objName to the name of the form I was searching):

DoCmd OutputTo A_MODULE, "Form." & objName,_
    A_FORMATTXT, "Formcode.txt"

I then read the resulting text file one line at time. As each line of the module was read back in, I checked it for the required string and any Function/Sub headers and footers. When I found a procedure name, I extracted it and set a flag to say that a procedure was being processed. When I found the procedure footer, I set the flag back to false (see Listing 2).

Listing 2. Searching CBF.

Function FormModule (strSearch As String, _
objName As String)

Dim dbCode As Database
Dim rsOutput As RecordSet
Dim i As Integer
Dim strSb As String
Dim strEd As String
Dim strFc As String
Dim strProcName As String
Dim fProc As Integer
Dim strRec As String

Set dbCode = CodeDb()
Set rsOutput = _
  dbCode.OpenRecordSet("Usys_Rat_Objects")

strSb = "Sub"
strEd = "End "
strFc = "Function"
fProc = False
strProcName = "[declarations]"

i = 0

DoCmd OutputTo A_MODULE, "Form." & objName, _
  A_FORMATTXT, "formcode.txt"

Open "formcode" For Input As #1

Do While Not EOF(1)
  i = i + 1
  Line Input #1, strRec
'searching for a new procedure header
  If (InStr(Trim(strRec), strSb) = 1 Or _
    InStr(Trim(strRec), strFc) = 1) And _
   (Not fProc) Then
'found a new procedure header so set the flag
    If (InStr(Trim(strRec), strSb) = 1 And _
        Mid(Trim(strRec), 4, 1) = " ") Or _
        (InStr(Trim(strRec), strFc) = 1 And _
        Mid(Trim(strRec), 9, 1) = " ") Then
'if the procedure name should be one character 
'after Sub or Function.
        strProcName = IIf(InStr(strRec, strSb), _
         Mid(strRec, InStr(strRec, strSb) + 4), _
         Mid(strRec, InStr(strRec, strFc) + 9))
        fProc = True
      End If
  End If

'found the end of the procedure so reset the flag
  If InStr(strRec, strEd & strSb) or _
   InStr(strRec, strEd & strFc)Then
    i = 0
    fProc = False
  End If

  If InStr(strRec, strSearch) And fProc Then
    rsOutput.AddNew
    rsOutput ("ParentName") = objName
    rsOutput ("ParentObj") = "Form Module"
    rsOutput ("ProcName") = strProcName
    rsOutput ("LineNo") = i
    rs("Line") = RatStripChr(RatRec)
    rsOutput.Update
  End If
Loop

Close #1

rsOutput.Close

Kill "formcode.txt"
End Function

Once again, when the required string was found, any control characters were stripped from the line of code before adding it to the search table.

Access contains a number of system tables, including MSysMacros, MSysIMEXSpecs, MSysIMEXColumns, and MsysRelationships (see Figure 2). These tables hold information about any Macros, Import/Export specifications, and Table Relationships that have been created. I searched these tables by reading them one record at a time and searching each field for the required string.

Figure 2: Access system objects

The MSysMacro table holds a number of records for each macro. A record with an ActionNo of 0 denotes the start of a new Macro script (you can get the macro name from the Scriptname field of this record). The Label, Expression, and Argument fields of the following records can then be searched for the required string until an ActionNo of 0 is again found.

The Import/Export specifications are held in two tables. The MSysIMEXSpecs holds one record for each specification and includes the name of the specifi-cation and data type formatting information. The MSysIMEXColumns holds field names, if these have been used. The Fixed Width file format, for instance, requires field names. The SpecName field of the MSysIMEXSpecs table holds the name of the Import/Export specification. Each specification is given a unique identifier, which is held in the SpecId field of the table. The SpecId can be used to retrieve the relevant records from the MSysIMEXColumns table. If no records are found, then there's no further processing required for the specification, and the process can move on to the next record in the MSysSpecs table. If a matching record is found, the FieldName field of the MSysColumns table can be searched for the required string (see Listing 3).

Listing 3. Searching Import/Export specs.

Sub ImEx(strSearch As String)

Dim dbCurr As Database
Dim dbCode As Database
Dim rsSpc As Recordset
Dim rsCol As Recordset
Dim rsOutput As Recordset
Dim strSpec As String

Set dbCurr = CurrentDB()
Set dbCode = CodeDb()
Set rsSpc = dbCurr.OpenRecordset("MsysImexSpecs")
Set rsOutput = dbCode.OpenRecordSet _
   ("USys_Rat_Objects")

If rsSpc.RecordCount > 0 Then
  Do Until rsSpc.EOF
    If InStr(rsSpc("SpecName"), strSearch) <> 0 Then
      rsOutput.AddNew
      rsOutput("ParentName") = rsSpc("SpecName")
      rsOutput("ParentObj") = "IMEX"
      rsOutput("Line") = "Import/Export spec Name"
      rsOutput.Update
    End If
    Set rsCol = RatDb.OpenRecordset _
     ("Select * From MsysImexColumns " & _
      "Where SpecId = " & rsSpc("SpecId") & ";")
        
    If rsCol.RecordCount > 0 Then
      Do Until rsCol.EOF
        If InStr(rsCol("FieldName"), strSearch) <> 0 _
        Then
          rsOutput.AddNew
          rsOutput("ParentName") = rsSpc("SpecName")
          rsOutput("ParentObj") = "IMEX"
          rsOutput("ProcName") = rsSpc("FieldName")
          rsOutput("Line") = _
           "Field name in Import/Export specification"
          rsOutput.Update
        End If
        rsCol.MoveNext
      Loop
    End If
    rsSpc.MoveNext
  Loop

End If

rsSpc.Close
rsOutput.Close

End Sub

Table Relations are held in the MSysRelationships table, where each record represents a table join. The szObject field holds the main Table name, the szColumn holds the main table's join Field name, the szReferencedObject holds the related Table name, and szReferencedColumn holds the related table's join Field name. Listing 4 shows the routine for searching this table.

Listing 4. Searching relations.

Sub TableRelations(strSearch As Search)

On Error GoTo Err_TableRelations

Dim dbCode As Database
Dim dbCurr As Database
Dim rsOutput As Recordset
Dim rsRelation As Recordset
Dim strObj As String
Dim strRef As String

Set dbCurr = CurrentDB()
Set dbCode = CodeDB()
Set rsOutput = RatCode.OpenRecordset_
("Usys_Rat_Objects")
Set rsRelation = RatDb.OpenRecordset_
("MsysRelationships")

strRef = ""
strObj = ""

If rsRelation.RecordCount > 0 Then
  Do Until rsRelation.EOF
    If InStr(rsRelation("szObject"), strSearch) <> 0 _
     Then
      If strObj <> rsRelation("szObject") Then
        strObj = rsRelation("szObject")
        rsOutput.AddNew
        rsOutput("ParentName") = _
           rsRelation("szObject")
        rsOutput("ParentObj") = "Relationship"
        rsOutput("Line") = "Table Name"
        rsOutput.Update
      End If
    End If
    If InStr(rsRelation("szColumn"), strSearch) <> 0 _
     Then
      rsOutput.AddNew
      rsOutput("ParentName") = rsRelation("szObject")
      rsOutput("ParentObj") = "Relationship"
      rsOutput("ProcName") = rsRelation("szColumn")
      rsOutput("Line") = "Join Field"
      rsOutput.Update
    End If
        
    If InStr(rsRelation("szReferencedObject"), _
     strSearch) <> 0 Then
      If strRef <> rsRelation ("szReferencedObject") _
       Then
        strRef = rsRelation("szReferencedObject")
        rsOutput.AddNew
        rsOutput("ParentName") = _
          rsRelation("szReferencedObject")
        rsOutput("ParentObj") = "Relationship"
        rsOutput("Line") = "Table Name"
        rsOutput.Update
      End If
    End If

    If InStr(rsRelation("szReferencedColumn"), _
     strSearch) <> 0 Then
      rsOutput.AddNew
      rsOutput("ParentName") = _
       rsRelation("szReferencedObject")
      rsOutput("ParentObj") = "Relationship"
      rsOutput("ProcName") = _
        rsRelation("szReferencedColumn")
      rsOutput("Line") = "Join Field"
      rsOutput.Update
    End If
    rsRelation.MoveNext
  Loop
End If

rsOutput.Close
rsRelation.Close

Exit_TableRelations:
  Exit Function

Err_TableRelations:
  MsgBox Error$
  Resume Exit_TableRelations

End Function

I've included a copy of the utility and its Help file in this month's Subscriber Downloads available at www.pinpub.com/access.

Although this tool currently doesn't address every control or property, I'm continuing to enhance the product. It's proving to be a terrific addition to my Access toolkit. More importantly, developing this tool has given me the opportunity to explore the way in which Access stores all the components that make up a database. Even more than using it, creating this tool has been an invaluable experience.

Download sample code for this article here.

Peter Bryant works as an Access developer for the Finance Systems department at Somerfield Stores Ltd. peter.bryant@somerfield.co.uk.