[AccessD] List all tables and their fields

[Bill Benson]

It does what it should but segregating into those tables that are linked
and those that are local can also be accomplished by testing for
tbl.Connection <> “”


On Fri, Mar 17, 2023 at 4:52 AM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> 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>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>