How can I get a list of fields AND respective tables used in an MS Access 2010 database?

Question

I know this is how you get a list of all the tables in the database (and more if you modify the critieria:

SELECT *
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0));

But is there a similar way to get a list of fields and what tables they belong to? I'm thinking this would be very usesful for troubleshooting and doing quick audit (i.e. answering "what other tables is this field or group of fields being used in"). But for whatever reason could not find an answer on the web.

If someone has a relatively simple way to do it through VBA, that can be an option too. Thanks.

Answer 1

Unfortunately, the fieldnames are not available through a nice, compact SQL Query. This code will print the table name and each field on a separate line in the debug window.

Private Sub ShowTableFields()

Dim db As Database
Dim tdf As TableDef
Dim x As Integer

Set db = CurrentDb

For Each tdf In db.TableDefs
   If Left(tdf.Name, 4) <> "MSys" Then ' Don't enumerate the system tables
      For x = 0 To tdf.Fields.Count - 1
      Debug.Print tdf.Name & "','" & tdf.Fields(x).Name
      Next x
   End If
Next tdf
End Sub

Answer 2

In this particular case, you may find ADO Schemas useful.

This will list fields and some properties for a particular table. Be careful of data types, you may not get an exact match to DAO types. You will need a reference to Microsoft ActiveX Data Objects x.x Library, unless you use late binding.

Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset

   Set cn = CurrentProject.Connection

   Set rs = cn.OpenSchema(adSchemaTables, _
       Array(Empty, Empty, Empty, "tablenamehere"))

   While Not rs.EOF
       Debug.Print rs!table_name; "   desc=  "; rs!Description
       Set rs2 = cn.OpenSchema(adSchemaColumns, _
           Array(Empty, Empty, "" & rs!table_name & ""))
       While Not rs2.EOF
           Debug.Print "     " & rs2!Column_Name
           Debug.Print "     " & rs2!Data_Type
           Debug.Print "     " & rs2!Description
           Debug.Print "     " & rs2!Is_Nullable
           rs2.MoveNext
       Wend
   rs.MoveNext
   Wend
   rs.Close
   Set cn = Nothing

You can also look at things the other way around and get a list of tables that contain a particular field.

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strTempList As String

   On Error GoTo Error_Trap

   Set cn = CurrentProject.Connection

   'Get names of all tables that have a column called <SelectFieldName>
   Set rs = cn.OpenSchema(adSchemaColumns, _
   Array(Empty, Empty, Empty, SelectFieldName))

   'List the tables that have been selected
   While Not rs.EOF
       'Exclude MS system tables
       If Left(rs!Table_Name, 4) <> "MSys" Then
           strTempList = strTempList & "," & rs!Table_Name
       End If
       rs.MoveNext
   Wend

   ListTablesContainingField = Mid(strTempList, 2)

From: http://wiki.lessthandot.com/index.php/ADO_Schemas

Stackoverflow has quite a bit on the subject: https://stackoverflow.com/search?q=%5Bms-access%5D+schema