Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Count columns per table

80 views
Skip to first unread message

musicloverlch

unread,
Mar 22, 2021, 1:50:32 PM3/22/21
to
I've figured out how to count the number of rows in each table, but how do I count the number of columns in each table? I don't want to do it table by table.

Thanks,
Laura

Ron Weiner

unread,
Mar 22, 2021, 4:56:18 PM3/22/21
to
musicloverlch wrote :
I don't understand exactly what you are after. But, the code below
will iterate each table in the database and print the table name an
number of columns in the table. Hopefully this gets you going.

Rdub

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
' ignore system and temporary tables
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
Debug.Print tdf.Name, tdf.Fields.count
End If
Next
Set tdf = Nothing
Set db = Nothing

--
This email has been checked for viruses by AVG.
https://www.avg.com

musicloverlch

unread,
Mar 23, 2021, 9:31:09 AM3/23/21
to
Thanks so much! I'm doing an analysis of my giant Access database so I can see how much further I can push it.

Ron Paii

unread,
Mar 24, 2021, 9:11:48 AM3/24/21
to
If you are pushing column count limits, I would seriously consider restructuring your tables.

Keith Tizzard

unread,
Mar 24, 2021, 10:51:45 AM3/24/21
to
Glad you have it sorted. I am intrigued to know what you mean by 'giant'. How are you measuring the size of your database?

Jim

musicloverlch

unread,
May 17, 2021, 5:18:16 PM5/17/21
to
I have a lot of linked tables and I wanted one list of how many columns they had in each table. Nothing had more than 100 so that's good. As for the db itself, the front end is about 200 MBs and the backend is split among five 1 to 1.5 GB databases. It is stable and has run fine for years.

Thanks everyone!

RobertoA

unread,
May 19, 2021, 2:17:02 AM5/19/21
to
Il 17/05/2021 23:18, musicloverlch ha scritto:
> I have a lot of linked tables and I wanted one list of how many columns they had in each table. Nothing had more than 100 so that's good. As for the db itself, the front end is about 200 MBs and the backend is split among five 1 to 1.5 GB databases. It is stable and has run fine for years.
>
> Thanks everyone!
>

1 to 1.5 of MDB / ACCDB files with data ?

musicloverlch

unread,
May 19, 2021, 9:53:54 AM5/19/21
to
I don't quite understand what you are asking. The frontend database is about 100 megabytes and there are about 5 gigabytes of data in the backend. I have the 5 gigabytes split among 5 backend databases.

RobertoA

unread,
May 19, 2021, 9:57:01 AM5/19/21
to
When you say "..backend databases..", do you mean they are MDB or ACCDB
files?
0 new messages