Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I know I've seen this somewhere but I've forgotten... Does anyone know offhand?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    Hans,

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

    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Maximum number of records in a table... (97 SR-2)

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

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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.
    Wendell

  8. #8
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Maximum number of records in a table... (97 SR-2)

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

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Maximum number of records in a table... (97 SR-2)

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •