[AccessD] List all tables and their fields

[Arthur Fuller]

Stuart,
As always, I sit at your feet in awe. Your brief message mentions several
things that at best I have heard of, but sometimes not even that.... Or
maybe I once knew of them and the senility is winning the day, the day
meaning this particular day.

I was about to go to sleep (I've done 3 all-nighters in the past five
days), but now I have to look up some stuff.
a) I have no clue regarding a Numbers table or why I need one and what
purpose it would serve.
b) I know that I deviate from standard practice in one respect, and it's a
theoretical battle in which I will not surrender easily, This concerns
defining columns as LookUps of various types. Perhaps the majority of
developers see this as an FE issue. Not me. I define as much as possible in
the BE, including lookups as combo boxes or list boxes, or more trivially,
phone numbers, credit card formats and digit sequences, upside down
addresses (this is one I get a lot of flak on, but I stand my ground: to
take one example, the most popular city name in the USA is Washington, with
88 occurrences. The second most popular is Springfield, which is why The
Simpsons live there -- it would have been too politically loaded to locate
them in Washington, particularly the DC variation, where the majority by
far is black.
In the app I'm currently working on, whose old model used to have separate
columns for City and State, and no lookups, thereby enabling all sorts of
nonsensical entries such as Nome, Alabama. So I ask for the state or
province first, and then I can restrict the list of cities accordingly.
It's counter-intuitive, but it has advantages. If I ask for Country first,
based on an easily obtained list of countries, then I immediately know its
provinces, states, cantons, etc., not to mention its phone number mask.

I digress into details, at the risk of losing the original point. There are
numerous occurrences of the City name Springfield in the USA. Best to ask
first for the state, and populate the City combo box relative to the
selected State, allowing for the user to add new Cities to the Cities
table. That's why I call this Upside-Down addresses, and it applies to even
larger geo areas: how many cities in the world are named London? Best to
begin with the nation, then its provinces or states or cantons, then the
cities or towns located there.

All this, IMO, belongs in the BE, not the FE. Over the years, I have
learned that the majority of db developers will disagree with me on this,
but I stick to my ground. This logic belongs in the BE not the FE. Not
least because every form or report or query that I create will "inherit"
this intelligence, and save me the hassle of re-defining it all over the
place in the FE. The same logic applies to similar situations, such as
Product Categories and Products. In short, "Drill Down".

Please explain why I need a Numbers table and what it ought to contain and
how I would use it. I am slightly embarrassed that I have no idea why this
might be useful, but am always ready to learn something new.

I cannot recall ever using the classic tblSysFile, and have no idea what it
contains, or how those contents might be useful. I shall Google to see what
I can learn that way, but your input would probably be better and more
accurate.

Everyone likes Switchboard Items, but I am unaware of its newer uses such
as menus. Would you mind explaining that or at least point me to something
to read?

Thanks.


On Fri, Mar 24, 2023 at 1:50 AM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:s

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


-- 
Arthur