[AccessD] List all tables and their fields

[Stuart McLachlan]

On 17 Mar 2023 at 2:48, Arthur Fuller wrote:

> I had some code that would list all tables and the fields for each of
> them. Now that I need it, I can't find it. I tried Microsoft Learn but
> couldn't find it there either.
> 
Just for fun, I asked ChatGPT:

<begin ChatGPT session>
Write a VBA function to list all the tables and fields in the current database


Here's a VBA function that lists all the tables and fields in the current database 
using DAO (Data Access Objects):
vbnet
Function ListTablesAndFields() 
Dim db As DAO.Database 
Dim tbl As DAO.TableDef 
Dim fld As DAO.Field 
Dim strOutput As String 
Set db = CurrentDb 
For Each tbl In db.TableDefs
     ' Exclude system tables 
    If Not (tbl.Name Like "MSys*" Or tbl.Name Like "~*") Then 
         strOutput = strOutput & tbl.Name & vbCrLf 
         For Each fld In tbl.Fields 
            strOutput = strOutput & " " & fld.Name & vbCrLf
       Next fld 
    End If 
Next tbl 
MsgBox strOutput 
End Function
To use this function, open the VBA editor in Access (Alt + F11), create a new 
module, and paste in the code. Then run the function by typing 
ListTablesAndFields() in the immediate window or by calling it from a macro or 
button. The function will output a message box containing a list of all the tables and 
fields in the current database (excluding system tables).
<end ChatGPT session>