Ken,

To step you precisely what I am doing, I first run this block of code
in any MDB.  It generates xyzTables and xyzFields and populates them
with complete schema information of all existing tables.

Public Sub DocumentTables()
   Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
   Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
   Dim t As String, strQuery As String
   Dim strTableSet As String, strFieldSet As String

   strSQL = "CREATE TABLE XYZTables " & _
            "(TableName TEXT CONSTRAINT " & _
            " PrimaryKey PRIMARY KEY, " & _
            " TableRecords Number) "
            CurrentDb.Execute strSQL, dbFailOnError

   strSQL = "CREATE TABLE XYZFields " & _
            "(TableName CHAR, " & _
            "FieldName CHAR, " & _
            "DataType CHAR, " & _
            "DataSize Number, " & _
            "FieldDesc CHAR) "
            CurrentDb.Execute strSQL, dbFailOnError

   strTableSet = "xyzTables"
   strFieldSet = "xyzFields"

   DoCmd.SetWarnings False
   DoCmd.runsql "DELETE * FROM [" & strTableSet & "];"
   DoCmd.runsql "DELETE * FROM [" & strFieldSet & "];"
   DoCmd.SetWarnings True

   Set db = CurrentDb
   Set rstTable = db.OpenRecordset(strTableSet, dbOpenDynaset)
   Set rstField = db.OpenRecordset(strFieldSet, dbOpenDynaset)

   Debug.Print

   For Each tbl In db.TableDefs
       Debug.Print tbl.Name
       If tbl.Attributes = 0 Then
           With rstTable
               .AddNew
               .Fields("TableName") = tbl.Name
               .Fields("TableRecords") = tbl.RecordCount
               On Error Resume Next
               .Fields("TableDesc") =
tbl.Properties("Description").Value
               On Error GoTo 0
               .Update
           End With
           For Each fld In tbl.Fields
               'add new record for each field in each table,
containing
               'table, field, data type of field
               With rstField
                   .AddNew
                   .Fields("TableName").Value = tbl.Name
                   .Fields("FieldName").Value = fld.Name
                   .Fields("DataType").Value =
GetFieldDataType(fld.Type)
                   .Fields("DataSize").Value = fld.Size
                   On Error Resume Next
                   .Fields("FieldDesc").Value =
fld.Properties("Description").Value
                    On Error GoTo 0
                   .Update
               End With
           Next fld
       End If
   Next tbl

   Debug.Print

   rstField.Close
   rstTable.Close
   Set fld = Nothing
   Set tbl = Nothing
   Set db = Nothing
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Then, this is where you received my sketchy description.

Using xyzField which is a complete list of all the tables and fields, I
am trying to put together a routine that skips through that table and
any time it finds "Brenda", or whatever, it appends the table and field
into that table. That is why I started with this approach:

Function InterrogateDB()
On Error Resume Next

    Dim db As Database
    Dim rsXYZFields As DAO.Recordset
    Dim mTable As String
    Dim mField As String
    Dim strSQL As String
    Dim strFIND As String
    strFIND = InputBox("Enter the field name fragment:")

    Set db = CurrentDb
   'Open the Table/Fields table

   Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

             With rsXYZFields
             .MoveFirst
             Do Until .EOF
                   mTable = "[" & Trim(.Fields(0)) & "]"
                   mField = "[" & Trim(.Fields(1)) & "]"

             strSQL = "Select " & mField & "from " & mTable & "where "
& mField Like strFIND
             CurrentDb.Execute strSQL, dbFailOnError
             If strSQL = True Then
                 strSQL = "INSERT INTO xyzResults ( TableName, " & _
                   "FieldName ) VALUES ( '" & mTable & "', '" & _
                   mField & "' )"
             End If
             .MoveNext
             Loop
              End With
           
     

End Function