Maximum number of records in a table… (97 SR-2)

Topic

New Reply

I know I’ve seen this somewhere but I’ve forgotten… Does anyone know offhand?

Reply | Quote

Replies

Trudi,

Type Specifications in the Help index, you’ll get an overview. There is no fixed limit to the number of records in a table, but the total size of an Access database (including system tables etc.) is limited to 2 GB.

Reply | Quote

Hans,

I looked at the specs in my Access 97 SR-2 is 1 GIG. Weird.

Ken

Reply | Quote

I’m sure that you’re right. I looked it up in my Access 2002 help. Anyway, by the time your table is approaching the limit, whether it be 1 GB on Access 97 or 2 GB on Access 2002, it is time to reconsider: either change the table structure, or migrate to SQL Server or another server database.

Reply | Quote

I’m sure that you’re right. I looked it up in my Access 2002 help. Anyway, by the time your table is approaching the limit, whether it be 1 GB on Access 97 or 2 GB on Access 2002, it is time to reconsider: either change the table structure, or migrate to SQL Server or another server database.

Reply | Quote

The limit was doubled in A2k and later to accommodate unicode. It boils down to roughly the same amount of data.

Reply | Quote

The limit was doubled in A2k and later to accommodate unicode. It boils down to roughly the same amount of data.

Reply | Quote

Trudi,

Type Specifications in the Help index, you’ll get an overview. There is no fixed limit to the number of records in a table, but the total size of an Access database (including system tables etc.) is limited to 2 GB.

Reply | Quote

There is one limit – IF you are using an autonumber field as a primary key;
please see Microsoft Knowledge Base Article – 97520
…The maximum value of the Counter data type is the same as the maximum value of a Long Integer. A Long Integer is 32 bits long and has a maximum positive value of 2,147,483,647 and a minimum negative value of -2,147,483,648.

If you use a Counter as the primary key, a table can contain up to four billion records. The maximum number of records in the table is also limited by the maximum size of a database.

regards,
Wayne

Reply | Quote

To amplify, if you are limited to a database size of 2GB, and you only have 1 field that is a small integer (2 bytes), you can have roughly 1,000,000,000 records, so you aren’t likely to every run out of autonumbers. But such a table isn’t very useful.

Reply | Quote

You can run out of autonumbers if you purge records from the table (e.g. archiving to a linked database). The autonumbers are not re-usable. We have in fact experienced this in our business.

regards,
Wayne

Reply | Quote

If you have run out of autonumbers, your database should have been moved to SQL Server instead of Access.

Reply | Quote

Hi Charlotte

If your Jet system is based on heavy usage of autonumber to link records and relationships, is there a SQL Serve equivalent?

John

Reply | Quote

SQL Server supports the same auto-increment field type, but if you have a system that has literally [/i]billions and billions[/i] of records, then you really want a different kind of primary key, probably multi field. There is something called a GUID which can be used – it is a 16 byte value – but in our experience caused some performance issues, at least with early versions of both Access and SQL. I’ve not revisited it with later versions.

Reply | Quote

SQL Server supports the same auto-increment field type, but if you have a system that has literally [/i]billions and billions[/i] of records, then you really want a different kind of primary key, probably multi field. There is something called a GUID which can be used – it is a 16 byte value – but in our experience caused some performance issues, at least with early versions of both Access and SQL. I’ve not revisited it with later versions.

Reply | Quote

Hi Charlotte

If your Jet system is based on heavy usage of autonumber to link records and relationships, is there a SQL Serve equivalent?

John

Reply | Quote

If you have run out of autonumbers, your database should have been moved to SQL Server instead of Access.

Reply | Quote

You can run out of autonumbers if you purge records from the table (e.g. archiving to a linked database). The autonumbers are not re-usable. We have in fact experienced this in our business.

regards,
Wayne

Reply | Quote

To amplify, if you are limited to a database size of 2GB, and you only have 1 field that is a small integer (2 bytes), you can have roughly 1,000,000,000 records, so you aren’t likely to every run out of autonumbers. But such a table isn’t very useful.

Reply | Quote

There is one limit – IF you are using an autonumber field as a primary key;
please see Microsoft Knowledge Base Article – 97520
…The maximum value of the Counter data type is the same as the maximum value of a Long Integer. A Long Integer is 32 bits long and has a maximum positive value of 2,147,483,647 and a minimum negative value of -2,147,483,648.

If you use a Counter as the primary key, a table can contain up to four billion records. The maximum number of records in the table is also limited by the maximum size of a database.

regards,
Wayne

Reply | Quote