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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

musicloverlch wrote :

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Thanks so much! I'm doing an analysis of my giant Access database so I can see how much further I can push it.


On Monday, March 22, 2021 at 3:56:18 PM UTC-5, Ron Weiner wrote:

musicloverlch wrote :

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, March 23, 2021 at 8:31:09 AM UTC-5, musicloverlch wrote:

Thanks so much! I'm doing an analysis of my giant Access database so I can see how much further I can push it.
On Monday, March 22, 2021 at 3:56:18 PM UTC-5, Ron Weiner wrote:

musicloverlch wrote :

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

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

If you are pushing column count limits, I would seriously consider restructuring your tables.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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



On Tuesday, 23 March 2021 at 13:31:09 UTC, musicloverlch wrote:

Thanks so much! I'm doing an analysis of my giant Access database so I can see how much further I can push it.
On Monday, March 22, 2021 at 3:56:18 PM UTC-5, Ron Weiner wrote:

musicloverlch wrote :

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 ?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, May 19, 2021 at 1:17:02 AM UTC-5, RobertoA wrote:

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 ?

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Il 19/05/2021 15:53, musicloverlch ha scritto:

On Wednesday, May 19, 2021 at 1:17:02 AM UTC-5, RobertoA wrote:

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 ?

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.

When you say "..backend databases..", do you mean they are MDB or ACCDB
files?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)