Results 1 to 5 of 5
  1. #1
    smz is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    2

    Advice on limitations of Access


    Hi,

    we are designing new database.
    For us it would be easiest to use MS Access as dbsoftware. Otherwise it is MySQL. Those are our options.

    We calculated that 5 tables will each reach 2 million records within two years.
    But the records are very simple and short:
    Col1 Col2 Col3 Col4 Col5
    (text) (date) (int) (int) (int)

    I know from experience that Access cannot decently handle >1 million long records.
    But maybe if its only short records, it does perform well regardless of number of records ?

    Please your advice. Access means less work and learning for us, but if performance so requires then we go MySQL.

    Thanks!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you have control over the client hardware that will be querying the back end you may be able to get away with it. I have noticed that desktops with onboard video processing do not do as well as machines with graphic cards, for instance. (Machines with graphic cards typically have more processing power and RAM too)

    I do not have much experience with tables >million but I have seen complaints about querying >2 Million. The fact that you do not have many columns and you are only using a couple large data types should help your cause.

    I think you will be OK if you build the app correctly. I have always used 2 Million as a cap. One thing you could do is put some test data in a few tables and do some benchmarks on a plane Jane PC. There are programs you can use to generate test data. Here is one that you have to pay for. There are others that are free and would work for your purpose.
    http://www.red-gate.com/products/sql...ata-generator/

    Also, consider the order SQL statements are logically evaluated. If you can avoid bringing the entire table into memory, you should be OK
    1. FROM
    2. WHERE
    3. GROUP BY
    4. HAVING
    5. SELECT
    6. ORDER BY


    Another option may be to use SQL Server Express as your back end. There are limitations. For instance, remote connections to the DB are not supported. However, I would consider this as an option if I were in your shoes.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How many users will be using the db simultaneously? >2 million records in 2 years, and then what (are you saying 1 million records/year?)

    You seem to know there is only 1 table; you know the structure. Can you take an available PC and create a dummy table then try a few queries. I think the 1-2 million short records may not be an issue, but if there is continued growth...??

  4. #4
    smz is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    2
    Ok, thanks for this info, also about the SQL logic.
    I guess we'll be fine for at least three years.
    Time enough to look into other db softs and possibly migrate the whole should it ever bee needed.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    We calculated that 5 tables will each reach 2 million records within two years.
    It is not about the number of records - it is the file size limitation: 2 GB.

    We currently have a BE that has 30 tables. A total of 2,033,442 records (as of today).

    One tables has 18 fields, the other table has 21 fields.
    The table with 21 fields has 1,829,558 records (as of today). That is 1.8+ million records in one table.

    No problems with performance.

    The BE file size is 290,880 KB.. just under 300MB. A long ways from the 2 GB limit!


    If you need more storage, I would look at SQL Express. The file size has been raised to 10GB. And it is free.

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

Similar Threads

  1. Replies: 1
    Last Post: 01-24-2014, 11:42 AM
  2. Chart Limitations / Alternatives?
    By sainttomn in forum Reports
    Replies: 3
    Last Post: 09-09-2011, 02:56 PM
  3. Multiple User Limitations
    By Nosaj08 in forum Access
    Replies: 12
    Last Post: 02-16-2010, 06:11 PM
  4. Date Selectable limitations
    By Robert_Clash in forum Access
    Replies: 0
    Last Post: 11-27-2007, 09:00 AM
  5. Access Limitations
    By kketchum in forum Access
    Replies: 2
    Last Post: 08-24-2006, 08:21 PM

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