Results 1 to 7 of 7

Thread: Table size

  1. #1
    Join Date
    Sep 2005
    Posts
    313

    Unanswered: Table size

    Hi,

    I go to help to search "Access specification"

    Table size: 2 gigabyte minus the space needed for the system objects
    Number of indexes in a table 32
    Number of fields in an index 10
    Size of an OLE Object field 1 gigabyte

    I don't quite understand,

    so what is the size of the table that store the records (data)?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Table size: 2 gigabyte minus the space needed for the system objects

    2GB would seem to be the maximum table size, but I seem to remember that 2GB is the maximum size for an MDB/MDE.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you have a project of such scope that you feel compelled to make this calculation, there is a fair chance that you should be considering alternate platforms...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Sep 2005
    Posts
    313
    I just want to know how many records they can input in one table.

    That's it.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Depends on the table. Is it a table of ridiculously huge cad drawings stuffed into OLE objects? In that case, probably 2. Is it a single field of bits? A few million.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by accessman2
    I just want to know how many records they can input in one table.

    That's it.
    how long is a piece of string? it depends on what you are storing in it as Teddy states.

    You can stuff approximately 2GB of data in a JET database before it sulks (can't accept more data), it may explode long before that (become unusable due to size or other contatraints).

    the precise calculation varies (depending on the number objects in the database, how well its maintained (AKA compact & repaired).

    if you had one table with an approximate size of say 1Kb, then you would be able to store anywhere up to 2 million rows, if your row size was 1 byte then anywhere upto 2,000 million rows. This actual number would undoubtably be less than the theoretical as it will vary with the number of indexed columns, the number of forms / reports / queries also in the db. you are always going to loose some space for the system tables.

    it also depends on table design, if you have a unique key (and if you don't then why not), then you are limited to the maximum number of unique values. if you use an autonumber as your unigue key then you are limited to the range of datatype long, wonder if the help system defines that? if you use say an 10 character alphanumeric then you have a maximum of 10 to the power of the number of characters your db can store - depends on the localisation of your db, if it supports UNICODE or standard ASCII characters. A standard ASCII character range is 128, UNICODE I forget. Not that I think JET still supports "ASCII only" though.

    If you are really concerned then do some calculations, look at each table, work out the number of bytes per row in a table, multiply that by the number of expected rows per table, add some fudge factor to cover the indexes, unreclaimed space and working space needed to run (I like to use 100% as a fudge factor to allow for unforeseen "growth" in sizing a db [if I can get away with that is]) then add it all together, if it comes to more than 2GB then you have a problem.

    In reality if you are concerned about breaking JET then you shouldn't be using JET as you data store in Access, switch to a server NOW if not earlier.
    Last edited by healdem; 12-13-05 at 04:02.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I a MDB file with 1 table ,

    that has 5 feilds text in the table

    record count or 742,429
    and 119,428 kb just compact it 115,724 kb now
    growing by 500 to 900 records a days An the Computer does the data entry for me.
    Last edited by myle; 12-13-05 at 22:46.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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