Search Multiple Tables in Access

Search Multiple Tables in Access

August 7, 2018

Dev Ashish has posted VBA code here, which can be used to search for a string in multiple Access tables.


In this example we have an Access database with multiple tables.  We want to find which tables contain the string, 'giraffe'.




Begin in Visual Basic by creating a new class with this code in it:


Public TableName As String
Public ColumnNames As New VBA.Collection
Public ResultRows As New VBA.Collection


Right click on the database name in the project list and select Insert . . .Class Module



In the Properties window at the lower right, rename the class module to, 'SearchResults'.




Next, create a new module with the full code listed below in it.   Modify the code on the line reading:


Set results = SearchAllTables("giraffe")





Press F5 to run the code.    Text will be generated in the immediate window listing the tables and lines on which the searched for string appears.







Sub TestSearchAllTables()
Dim results As VBA.Collection
Dim result As SearchResults
Dim i As Integer, j As Integer, k As Integer

Set results = SearchAllTables("An")
If results.Count > 0 Then
For i = 1 To results.Count
Set result = results.item(i)
With result
Debug.Print "***************"
Debug.Print "Result found in: " & .TableName
Debug.Print "***************"
For j = 1 To .ColumnNames.Count
Debug.Print .ColumnNames.item(j),
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
End With
Debug.Print "No records found"
End If
End Sub

Function SearchAllTables(criteria As String) As VBA.Collection
Dim rs As dao.Recordset
Dim tdf As dao.TableDef
Dim db As dao.Database
Dim fld As dao.Field
Dim sql As String, i As Integer, j As Integer
Dim doInclude As Boolean
Dim results As VBA.Collection
Dim item As SearchResults, items() As String
On Error GoTo ErrHandler

Set db = CurrentDb
Set results = New VBA.Collection

For Each tdf In db.TableDefs

doInclude = (Not CBool(tdf.Attributes And _
dbSystemObject)) And _
(Not CBool(tdf.Attributes And dbHiddenObject))
If (doInclude) Then
sql = "select * from [" & tdf.Name & _
"] where "
For Each fld In tdf.Fields
sql = sql & "[" & fld.Name & "] like '*" & _
criteria & "*' or "
sql = Left$(sql, Len(sql) - 3)
Set rs = db.OpenRecordset(sql)

If (rs.RecordCount > 0) Then
Set item = New SearchResults

item.TableName = tdf.Name
ReDim items(0 To rs.Fields.Count - 1)
For i = 0 To rs.RecordCount - 1
For j = 0 To rs.Fields.Count - 1
items(j) = rs.Fields(j).Value & vbNullString
item.ResultRows.Add items
For j = 0 To rs.Fields.Count - 1
item.ColumnNames.Add rs.Fields(j).Name
results.Add item:=item, Key:=tdf.Name
End If
End If

Set SearchAllTables = results

Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing

Exit Function
With Err
MsgBox "Error: " & .Number & vbCrLf & _
.Description, vbOKOnly Or vbCritical, "SearchAllTables"
End With
Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
End Function











Please reload

Contact Me With Your Litigation Support Questions:

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with