Search Multiple Tables in Access

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

Utility to Search Multiple Access Objects

If you have an Access database with a large number of tables, queries, and other objects, you may find it helpful to use the utility developed by Brent Spaulding and posted here on Utter Access. The .mdb file downloads in a zip file. After you've unzipped it, you can import it into your existing Access database by going to External Data . . . New Data Source . . . From Database . . . Access.

You'll have the option to import any objects from the selected database, 'SearchForText.mdb'. Select the 'Form' tab and choose, 'xdlgSearchForText_v42' , and then click OK.

A new form will be added to the Object menu. You can select one or more objects to search. If you search for a field name such as 'HR', you'll get a list of which tables it appears in.

If you search for the table name, you can easily get a list of all of the fields in the table, that you can then select and copy.

SQL to Create Multiple Fields in an Access Database

It’s not necessary to enter fields one by one in an Access table.  You can run SQL script to add multiple fields automatically.   

First go to Create . . . Query Design and open a new query design.  In the View menu on the left select  SQL View.   Enter the ALTER command to add fields to an existing table followed by ADD COLUMN and the new names & data types.  See this example:

ALTER TABLE Table1 ADD COLUMN yearID Memo, lgID Memo, teamID Memo, franchID Memo, divID Memo, Rank Memo, G Memo, Ghome Memo, W Memo, L Memo, DivWin Memo, WCWin Memo, LgWin Memo, WSWin Memo, R Memo, AB Memo, H Memo, 2B Memo, 3B Memo, HR Memo, BB Memo, SO Memo, SB Memo, CS Memo, HBP Memo, SF Memo, RA Memo, ER Memo, ERA Memo, CG Memo, SHO Memo, SV Memo, IPouts Memo, HA Memo, HRA Memo, BBA Memo, SOA Memo, E Memo, DP Memo, FP Memo, name Memo, park Memo

Click the floppy disk icon to save the query, then just double click it on the Query list to run it. 

In the table design view you’ll see a list of new fields like this.