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

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.

  • Are you specifically looking for SQL and/or VBA only? Did you try the database documenter? – CharlieRB Jun 14 '12 at 19:58
  • SQL solution is preferred but if that's not possible then VBA will do. The database documenter is what I use today (with options set to a minimum). But I was hoping to have it in tabular form (less noise) similar to when I pull from MSysObjects (as shown in my example). – daniellopez46 Jun 14 '12 at 20:49

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
  • Thanks for providing this VBA. But I tried running it and got an error on the Debug line. Was I suppose to modify something prior to running it? I see the first parm in Left function is "tbl_Name" and don't see where that is defined. Am I suppose to overwrite that and then this really only works with one table at a time? – daniellopez46 Jun 18 '12 at 17:50
  • I fixed the code - tbl_Name should have read tdf.Name. Debug.Print should simply send the output to the immediate window – SeanC Jun 18 '12 at 17:52
  • I copied and pasted your edited code and it still errored out on the same Debug line – daniellopez46 Jun 18 '12 at 18:18
  • What error does it give? I've just done a copy and paste (just to make sure it's not a syntax error), and it ran through without any problems. (I'm using Access 2007, but it should work on 97 or later) – SeanC Jun 18 '12 at 18:23
  • I took out the default "Option Compare Database" line at the top and now it works. Thank you! – daniellopez46 Jun 18 '12 at 20:41

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
   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

   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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.