Navigation:  Articles > Feb-1998 >

Searching Access

Previous pageReturn to chapter overviewNext page

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.
 

NOTE:  The download for this add-in is not available as a download.

 
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.
 

199802_pb1 Figure 1

 
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.
 
Table 1. The search report table.

Field name

Description

Parent Name

Table, Query, Form, Report, Macro, Module, IMEX specification name.

Parent Object

The object type. For example: Form, Report, and so on.

Procedure Name

Function or Subroutine or Control name. That is, IsSystemObjtec(),btnClose.

LineNo

Module line number the string occurs in.

Line

Where the search string occurs, that is, SQL string, Visual Basic code, Property value.

 
 
Table definitions
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.
 
Queries, forms, and reports
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.
 
Macros, Import/Export specs, and relations
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.

199802_pb2 Figure 2
 
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 download file DBRAT.ZIP.
 
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.
 
NOTE:  The download for this add-in is not available as a download.