database - Find a table when you know the name of a column?

Question

I have a DB in Access with an incredible amount of tables. Unfortunately, the creator used very non-descriptive names, so it's basically impossible to even guess what a table is for just by looking at its name. I need to urgently find a table that contains certain data, and I am pretty sure that I know the name of some of its columns, or at least words contained within the names of the columns. Basically, what I need is some kind of 'Search by column name in every table in the whole database', that shows all tables that contain certain column names.

Is there any way to accomplish this, before I start going one-by-one like a monkey?

Answer 1

This procedure will list the table name and column name for any columns whose names contain the text you supply. The results are printed in the Immediate window (go there with Ctrl+g)

Public Sub ListTablesWithColumnNamesContaining(ByVal pText As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
For Each tdf In db.TableDefs
    For Each fld In tdf.Fields
        If InStr(1, fld.Name, pText, vbTextCompare) > 0 Then
            Debug.Print tdf.Name & ":", fld.Name
        End If
    Next fld
Next tdf
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub