Count columns per table

41 views
Skip to first unread message

musicloverlch

unread,
Mar 22, 2021, 1:50:32 PMMar 22
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 PMMar 22
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 AMMar 23
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 AMMar 24
to
If you are pushing column count limits, I would seriously consider restructuring your tables.

Keith Tizzard

unread,
Mar 24, 2021, 10:51:45 AMMar 24
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 PMMay 17
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 AMMay 19
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 AMMay 19
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 AMMay 19
to
When you say "..backend databases..", do you mean they are MDB or ACCDB
files?
Reply all
Reply to author
Forward
0 new messages