Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: How many Tables is too many for Access?

    I'm a totally self taught access guy and I'm working in an office improving some existing access databases (yikes!)

    My managers have in mind to create one massive database which joins all of this information together (as of now A LOT of information is duplicated throughout the whole system)

    My first iteration of the process was to familiarize myself with all the databases and remake them in a way that used a few more normalization rules.

    I now think I need a good Front-End Back-End setup.

    Q: Can I put all tables (~30) into one Back-End Database or would it be better to create separate access files to handle this and then link them?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    30 tables does not seem too much.

    See:What are the limitations of MS Access?

    Have a nice day!

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Check out the Access Specifications.
    Quote Originally Posted by Access Speficitations
    Number of open tables 2048;
    the actual number may be less because of tables opened internally by Microsoft Access
    Me.Geek = True

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on my experience, there seems to be a practical limit somewhere around 300 tables. This isn't a design limit, it just seems to be where the people that administer databases using MS-Access seem to migrate to other platforms.

    -PatP

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I agree with Pat. My personal limit is about 150 though; I'll go to an SQL Server if it looks like the app is going to need >150 tables.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2009
    Location
    SLOVAKIA,Bratislava
    Posts
    70
    Hi , i am using ~ 3000 static tables in linked DB ,MS ACCESS 2003/SINGEL
    But ADO/DAO tables.refresh IS than significant slower...
    i am using separat table , with contents of this DB.
    In other words it is not very 'usefull' Dynamically add new tables into it
    15 years db-programmer(dBase,FoxPro,MS Access 2002/2003),Symbian C++

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The other issue with Access is the number of concurrent users. As the number of concurrent users go up, especially the number of consurrent users who could be writing to the datafile, the reliability goes down.

    And, this limit is about as solid as the Rock of Jello... Depending upon your network speed (networks with not a lot of available bandwidth, and wireless networks negatively impact reliability,) server types (Novell servers & Linux file servers seem to negatively impact reliability,) the manner in which the client's access the data file, and other factors, you can run into trouble with as few as a half-dozen users, or you can run at 30 or more users.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Mar 2009
    Location
    SLOVAKIA,Bratislava
    Posts
    70
    Hi , i am using ~ 3000 static tables in linked DB ,MS ACCESS 2003/
    SINGEL USER, Local Data
    *********************
    But my Problem was , i was able over NightRun to create only up to
    about ~19000 tables, than with next one 19001 was some not very clear Problem.It was not in One DB, it was ~ about 12 DBs.
    15 years db-programmer(dBase,FoxPro,MS Access 2002/2003),Symbian C++

Posting Permissions

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