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),
Next
Debug.Print
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
Next
Next
Debug.Print
End With
Next
Else
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 "
Next
sql = Left$(sql, Len(sql) - 3)
Set rs = db.OpenRecordset(sql)

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

item.TableName = tdf.Name
rs.MoveFirst
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
Next
item.ResultRows.Add items
rs.MoveNext
Next
For j = 0 To rs.Fields.Count - 1
item.ColumnNames.Add rs.Fields(j).Name
Next
results.Add item:=item, Key:=tdf.Name
End If
rs.Close
End If
Next

Set SearchAllTables = results

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

Exit Function
ErrHandler:
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

Some elements on this page did not load. Refresh your site & try again.

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com