Get size of table (2000)

Topic

New Reply

I don’t know of any way to do it within Access, but one way you could find out is to create an empty database and import just that table. Then compact the database, and the size of the database will be reasonably close to the size of the table.

Reply | Quote

Replies

(Edited by HansV to make subject a bit more descriptive than “Access”)

How do you get the size (megabytes) of a table?

Reply | Quote

Since the limits generally apply to the entire database long before they get to the single table level, what would be the purpose of finding out the size of a particular table?

Reply | Quote

Since the limits generally apply to the entire database long before they get to the single table level, what would be the purpose of finding out the size of a particular table?

Reply | Quote

We use Access as a front end with SQL Server as a backend for large database applications. When we’re using Access to prototype an application, we’re often given many very large tables. If we need to create several mdb files to get around the 2 gigabyte mdb limit, we want to know how large each table is so we can combine them rationally in new mdb files. Also, append queries can increase an existing table beyond the 1 gigabyte table limit. Again, we’d like to know when a given table begins getting large enough for us to be concerned about possibly exceeding the 1 gigabyte limit as we add new records.

Thanks for your response and interest.

Reply | Quote

The 1 gigabyte limit was Access 97. In 2000 and later, it is 2 Gb because of unicode.

Reply | Quote

Interesting. The Access 2000 Help File still indicates a 1 gigabyte single table size limit.

Btw, is the Access 2 gigabyte limit an Access constraint, or is it tied to FAT32? If the latter, will be seeing a much larger file limit as everybody moves to NTFS?

Thanks again.

Reply | Quote

As far as I know, the limit is built into Access, it is not caused by the file system. Access 2002 help indicates 2 GB.

Reply | Quote

Access help indicates 2GB for an Access Database (mdb file) but in the section Table specifications it says 1GB for a table

Reply | Quote

Yes, you’re correct. Good point!

Reply | Quote

Yes, you’re correct. Good point!

Reply | Quote

Access help indicates 2GB for an Access Database (mdb file) but in the section Table specifications it says 1GB for a table

Reply | Quote

As far as I know, the limit is built into Access, it is not caused by the file system. Access 2002 help indicates 2 GB.

Reply | Quote

Interesting. The Access 2000 Help File still indicates a 1 gigabyte single table size limit.

Btw, is the Access 2 gigabyte limit an Access constraint, or is it tied to FAT32? If the latter, will be seeing a much larger file limit as everybody moves to NTFS?

Thanks again.

Reply | Quote

The 1 gigabyte limit was Access 97. In 2000 and later, it is 2 Gb because of unicode.

Reply | Quote

Is there a reason for not creating a SQL Server test database? We tried you approach for a while, but finally concluded that it wasn’t worth all the pain and strain when you start dealing with large million record tables. In addition, when you want to take an application live, it’s much easier to simply switch the ODBC data source than it is to upsize everything and then try to create data constraints, relationships and triggers and views. You might also consider using the MSDE since it is SQL Server dumbed down a bit.

Reply | Quote

Is there a reason for not creating a SQL Server test database? We tried you approach for a while, but finally concluded that it wasn’t worth all the pain and strain when you start dealing with large million record tables. In addition, when you want to take an application live, it’s much easier to simply switch the ODBC data source than it is to upsize everything and then try to create data constraints, relationships and triggers and views. You might also consider using the MSDE since it is SQL Server dumbed down a bit.

Reply | Quote

We use Access as a front end with SQL Server as a backend for large database applications. When we’re using Access to prototype an application, we’re often given many very large tables. If we need to create several mdb files to get around the 2 gigabyte mdb limit, we want to know how large each table is so we can combine them rationally in new mdb files. Also, append queries can increase an existing table beyond the 1 gigabyte table limit. Again, we’d like to know when a given table begins getting large enough for us to be concerned about possibly exceeding the 1 gigabyte limit as we add new records.

Thanks for your response and interest.

Reply | Quote

Or rather, the difference in size between the empty database and the database with the table (both after compacting.)

Reply | Quote

Or rather, the difference in size between the empty database and the database with the table (both after compacting.)

Reply | Quote