[AccessD] List all tables and their fields

[Stuart McLachlan]

I frequently mix local and linked tables.

Local tables for thing like a "numbers table" (every application should have one!), lookup 
values that are an integral part of the application and should not be changed by end users 
(such as some Geodata), the classic tblSysFile that holds application settings, ....

And let's not forget the good old legacy [Switchboard Items] or it's more recent variants for 
menus etc  :)



On 23 Mar 2023 at 20:52, Arthur Fuller wrote:

> That's a good idea, although I seldom mix local and linked tables. But
> some do, so I'll add that.
> 
> On Thu, Mar 23, 2023 at 7:56PM Bill Benson <bensonforums at gmail.com>
> wrote:
> 
> > 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
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> 
> 
> -- 
> Arthur
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com