Find a table when you know the column name?

Question

I have an Access database with an incredible amount of tables. Unfortunately, the creator used very descriptive names, so in principle it is impossible to even guess what a table is just by looking at its name. I urgently need to find a table containing certain data, and I'm sure I know the names of some of its columns, or at least the words contained in the column names. Basically, I need some kind of "Search by column name in each table in the entire database", which shows all tables containing specific column names.

Is there a way to do this before I start walking one by one like a monkey?

Answer 1

This procedure will indicate the table name and column name for any columns whose names contain the text you provide. Results are printed in the Immediate window (see 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 

Answer 2

There is a way to find all tables with a specific column name, but this requires some code (as opposed to being able to simply execute a query).

First you need to make the system tables “visible” in the database. You do not specify which version of MS Access is used, but the Option dialog should allow something along these lines .

This provides an MSysObjects table that contains all the user table names.

You probably want to iterate over all user tables, passing the names there into some code that opens the tables as DAO.TableDef objects and parses the attributes of the corresponding Fields collection.

Allen Browne has some VBA code that displays these attributes . You can customize this for your needs.