Results 1 to 9 of 9
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Maximum table length

    Good day;




    Doing a time line DB, and I have over 1/4 Million main records each having a minimum of 5 dates, with some (depending on detail) going into 20’s of dates.
    MS Access AutoNumber is a Long Integer without a sign giving about 8 Million numbers and therefore records per table. This would give an average of 32 event records per main item.
    Being in the non null camp, doing a table of dates, for the common 5 events, and a table of Events / Dates for the rest feels wrong (Split data & nulls). Using just a Event table creates a very long table and a weak point (1 table many times longer than any other table, first out of range).
    Thoughts ? Am I missing or got something wrong? Another way?


    Date Event fields examples
    Birth, Service start, Marriage, Service end, Death, Located at date, Campaign, Rank, Unit assigned, etc

    Thanks for looking

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    There is no limit to the number of data records in Access. The constraint is on total Database Size (2 GB), not on Record Count.
    (Note that here is a limit to the number of Fields (Columns) you can have, but I don't think that comes into play here).

    Depending on what you want/need to do with this data, I can see approaching it in one of two ways:

    Have one big Event table, where you really only have about three or four fields: Record ID, ID, Event, Date
    Have a separate table for each event

    If you are going to need to put events together, or search across all events, I would think a single table would be better. Otherwise, you would have a huge Union query to sew them all together.

    Also, if you may be adding new Events in the future, I think a single table would be better, as any solution where you have to create new Tables or Fields because of new data of similar type is not best practice (it should be expandable without having to change your Objects).

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Okay on the no length, but what happens to the auto number after 8M?

    The design I am eyeing is ID, PersonID, EventID, EventDate, DateAcc so one person would have a minimum of “5” records and no nulls. There would also be an Event table controlling the event descriptions.

    Again Thanks for replying

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It looks like it can handle little over 4 BILLION records: https://social.msdn.microsoft.com/Fo...orum=accessdev
    So, I would reckon to safe you are probably safe. If you have more than that, I imgaine you will exceed the 2 GB size limit, and probably cannot use Access anyway (and should be using something like SQL instead, at least to house the data).

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Do you really need A/N field? It's not unheard of to use data as pk and fk fields. Any unique ID for the person? Registration # perhaps?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Quote Originally Posted by JoeM View Post
    It looks like it can handle little over 4 BILLION records: https://social.msdn.microsoft.com/Fo...orum=accessdev
    So, I would reckon to safe you are probably safe. If you have more than that, I imgaine you will exceed the 2 GB size limit, and probably cannot use Access anyway (and should be using something like SQL instead, at least to house the data).
    Thanks Joe Silly me, talking using "I thinks" without looking something up. Yup at 4 B I would have other problems

    Thanks Micron I was thinking along that line as well, but 4 B is 4 B

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    don't forget - each record will use x number of bytes (dates/singles/decimals being among the larger you might have) + overhead + bloat + field captions + etc.
    You will probably be o
    k but the number of records has less to do with A/N max count than total db size I think. Far less if you use attachment fields. Just sayin' it ain't gonna be 4 B.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Although the theoretical limit is over 4 billion records using positive and negative values for long integer, I think the practical limit will be far less than that due to the 2GB datafile size limit. The largest table I have is around 28 million records and that takes up almost the entire size of a 1.8GB database. Obviously the overall size will also depend on the number of fields and datatypes used.
    Although I've never experienced negative incrementing autonumbers when the positive long integer limit is reached, I have experienced a similar issue with report page numbers for very large reports of 50,000+ pages. Page numbering uses integer numbers so if your report exceeds 32767 the page numbering goes negative.
    If interested, see this article on my website http://www.mendipdatasystems.co.uk/n...ges/4594515908

    EDIT
    Micron made the same point as my first paragraph but got there quicker
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks Again All

    Colin, that page numbering thing is interesting, in fact this whole thread is.

    Micron, Ya the limit will be reach in other ways. but its better to prevent a design from creating its own problems
    Thanks Again

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-12-2018, 05:21 PM
  2. Minimum & Maximum Row Value
    By Thompyt in forum Programming
    Replies: 8
    Last Post: 05-10-2016, 01:08 PM
  3. Replies: 3
    Last Post: 05-21-2015, 09:11 AM
  4. Replies: 1
    Last Post: 11-18-2011, 08:12 AM
  5. How do I return the maximum value
    By kam in forum Queries
    Replies: 2
    Last Post: 03-17-2010, 07:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums