Access Limit


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: Access Limit

  1. #1
    Amrita Guest

    Access Limit


    We are using access for a network database application, and it continually
    gets slower. I was informed that the max. that access 97 allows is 250,000
    records.
    Our database is not necessarily normalized and has 125 tables with the largest
    table having about 225,000 records.
    At what point should we start getting concerned about the database and its
    performance? We compact and repair it almost weekly due to the size.
    I would appreciate any information.
    Thanks

    Amrita

  2. #2
    Kevin Guest

    Re: Access Limit


    From the Access97 help file do a search for "maximum database size"
    Here is an extract from that search
    **************************************
    Database specifications

    Attribute Maximum
    Database (.mdb) file size 1 gigabyte. However, because your database can
    include linked tables in other files, its total size is limited only by available
    storage capacity.
    Number of objects in a database 32,768
    Modules (including forms and reports with the HasModule property set to True)
    1024
    Number of characters in an object name 64
    Number of characters in a password 14
    Number of characters in a user name or group name 20
    Number of concurrent users 255

    Table specifications

    Attribute Maximum
    Number of characters in a table name 64
    Number of characters in a field name 64
    Number of fields in a table 255
    Number of open tables 1,024. The actual number may be less because of tables
    open internally by Microsoft Access.
    Table size 1 gigabyte
    Number of characters in a Text field 255
    Number of characters in a Memo field 65,535 when entering data through the
    user interface;
    1 gigabyte when entering data programmatically.
    Size of an OLE Object field 1 gigabyte
    Number of indexes in a table 32
    Number of fields in an index 10
    Number of characters in a validation message 255
    Number of characters in a validation rule 2,048
    Number of characters in a table or field description 255
    Number of characters in a record (excluding Memo and OLE Object fields) 2,000
    Number of characters in a field property setting 255
    **************************************
    I can give you a personal example.

    We have an Access97 database with 4 tables. It has been collecting data for
    over 2 years.
    It gets repaired and compacted about once a month. It is updated nightly
    with about 8000 records.
    The update takes about 20 mintes partly due to network traffic.
    Running on a local machine would proably improve the time but it occurs at
    3 in the morning so who cares.

    Generally all user interaction (for Reporting) is read only. Performance
    is very fast pulling back a record selection of up to 100 records
    takes under a second.
    The file size is 363 megs. The largest tabel has 951,997 records. The next
    largest has 11,909.
    The other 2 tables are relatively small.

    I've considered going to SQL Server sometime but until performance gets to
    be a problem I'm going to leave it alone.

    You may want to run the Access Performance wizard (Tools | Analyze | Performance
    - menu) to see if it suggests any additional idexes etc.

    Kevin


    "Amrita" <anaimpa1@hotmail.com> wrote:
    >
    >We are using access for a network database application, and it continually
    >gets slower. I was informed that the max. that access 97 allows is 250,000
    >records.
    >Our database is not necessarily normalized and has 125 tables with the largest
    >table having about 225,000 records.
    >At what point should we start getting concerned about the database and its
    >performance? We compact and repair it almost weekly due to the size.
    >I would appreciate any information.
    >Thanks
    >
    >Amrita



  3. #3
    Douglas J. Steele Guest

    Re: Access Limit

    Kevin's already given you good advice. I just wanted to add that you should
    NEVER repair a database unless Access explicitly asks you to. You can
    actually cause corruption by repairing unnecessarily.

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Amrita" <anaimpa1@hotmail.com> wrote in message
    news:3a631363$1@news.devx.com...
    >
    > We are using access for a network database application, and it continually
    > gets slower. I was informed that the max. that access 97 allows is 250,000
    > records.
    > Our database is not necessarily normalized and has 125 tables with the

    largest
    > table having about 225,000 records.
    > At what point should we start getting concerned about the database and its
    > performance? We compact and repair it almost weekly due to the size.
    > I would appreciate any information.
    > Thanks
    >
    > Amrita




  4. #4
    Arthur Wood Guest

    Re: Access Limit


    Amrita,
    You say that you are using 125 tables. How many joins are you using in
    your queries? If you have numerous joins, and the indexes are not properly
    set for the Join fields, then you can get poor performance. But 225,000
    is not en excessive number of records. If you are still experienceing problems,
    then you may want to look into upscaling to SQL Server, though this will
    involve some added expense.

    Arthut Wood

    "Amrita" <anaimpa1@hotmail.com> wrote:
    >
    >We are using access for a network database application, and it continually
    >gets slower. I was informed that the max. that access 97 allows is 250,000
    >records.
    >Our database is not necessarily normalized and has 125 tables with the largest
    >table having about 225,000 records.
    >At what point should we start getting concerned about the database and its
    >performance? We compact and repair it almost weekly due to the size.
    >I would appreciate any information.
    >Thanks
    >
    >Amrita



  5. #5
    Michael Harding Guest

    Re: Access Limit


    We have built several VERY large multi million record db's in A97. The secret
    is have NO relationships defined. Do all the RI by hand.


  6. #6
    Ronald Rossman Guest

    RE: Access Limit


    Douglas stated that you should never repair an Access database unless stated
    to do so by Access. This may be true in Access 97, but in Access 2000, the
    compact and repair have been joined into one process. We run databases on
    Acess 200 in multiple labs and compact nightly with no ill effects. Also,
    not having relationships defined in Access and doing the RI by hand does
    increase performance (We've seen it).

  7. #7
    Antony Guest

    Re: Access Limit


    No additional cost will be incurred if you upgrade to MSDE (freebie from MS
    - included on MS Office 2000 Pro + Prem)

    I've used MSDE on many occasions and for complex queries the perfomance boost
    over Access is very noticable.

    Antony


    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Amrita,
    > You say that you are using 125 tables. How many joins are you using

    in
    >your queries? If you have numerous joins, and the indexes are not properly
    >set for the Join fields, then you can get poor performance. But 225,000
    >is not en excessive number of records. If you are still experienceing problems,
    >then you may want to look into upscaling to SQL Server, though this will
    >involve some added expense.
    >
    >Arthut Wood
    >
    >"Amrita" <anaimpa1@hotmail.com> wrote:
    >>
    >>We are using access for a network database application, and it continually
    >>gets slower. I was informed that the max. that access 97 allows is 250,000
    >>records.
    >>Our database is not necessarily normalized and has 125 tables with the

    largest
    >>table having about 225,000 records.
    >>At what point should we start getting concerned about the database and

    its
    >>performance? We compact and repair it almost weekly due to the size.
    >>I would appreciate any information.
    >>Thanks
    >>
    >>Amrita

    >



  8. #8
    Landy Johnson Guest

    Re: Access Limit


    I have found that it isn't so much the size of the database that causes the
    problems, but the number of concurrent users. The last application I was
    involved with using Access97 as the database engine hit the wall (and hit
    it HARD) at 7-8 concurrent users. And I will also verify the manual RI performance
    boost.

    Do you use Access as the db engine with a VB front-end or do you use Access
    forms, reports, modules, etc. One thing I found in the past is that if you
    separate the Access tables from the Access application, it will run a bit
    better. Access seems to work better with linked tables than on native tables.
    No idea why. Also, it makes it easier to upgrade. Explain: one database
    contains only the tables and the other database contains the application,
    with all the tables from the first database linked in the second. Backup
    is only needed on the first (data) database, and to make changes to the app
    only requires a new second (application) database.

    As for the Repair. Always make certain you save the current copy before
    the Repair. Personal horror story: repaired the database, every indication
    it went well. But all the report totals were now grossly wrong! Seems Access
    "lost" about 6,000 records during the repair, but didn't report any errors.
    Luckily, I had pulled a copy for testing about 2 hours earlier. Never opened
    myself up for that disaster again! (And one more reason to always have your
    "offsite backups" up-to-date and your resume current <G>)

    Hope this helps.
    Landy

    "Amrita" <anaimpa1@hotmail.com> wrote:
    >
    >We are using access for a network database application, and it continually
    >gets slower. I was informed that the max. that access 97 allows is 250,000
    >records.
    >Our database is not necessarily normalized and has 125 tables with the largest
    >table having about 225,000 records.
    >At what point should we start getting concerned about the database and its
    >performance? We compact and repair it almost weekly due to the size.
    >I would appreciate any information.
    >Thanks
    >
    >Amrita



  9. #9
    Beth Guest

    RE: Access Limit


    Access 2000 has a neat feature that saves time compacting.
    <Tools><Options><General>
    The lower left corner has a "Compact on Close" check box.
    It takes a little longer to close a db, but it automates the compact.

    "Ronald Rossman" <rrossman@ficom.net> wrote:
    >
    >Douglas stated that you should never repair an Access database unless stated
    >to do so by Access. This may be true in Access 97, but in Access 2000, the
    >compact and repair have been joined into one process. We run databases on
    >Acess 200 in multiple labs and compact nightly with no ill effects. Also,
    >not having relationships defined in Access and doing the RI by hand does
    >increase performance (We've seen it).



  10. #10
    Michael Hodes Guest

    RE: Access Limit


    I've setup auto repair and compact utilities to run on some Access 97 database
    and the repair, even without prompting, works fine. Some of these databases
    have been in production for a couple of years.

  11. #11
    Michael Hodes Guest

    Re: Access Limit


    You've already received a lot of good feedback, so I'll say very little.
    I have many large databases (500+ MB and/or 1M+ records) and run a lot of
    complex queries.
    Access is a great tool, but when you're tables get really big, it is slow.

    Is there any way to compile summary tables during non-peak times and have
    the users query against the summary tables?

    Also, can you split the table horizontially and have the users query against
    the subsets?

    "Amrita" <anaimpa1@hotmail.com> wrote:
    >
    >We are using access for a network database application, and it continually
    >gets slower. I was informed that the max. that access 97 allows is 250,000
    >records.
    >Our database is not necessarily normalized and has 125 tables with the largest
    >table having about 225,000 records.
    >At what point should we start getting concerned about the database and its
    >performance? We compact and repair it almost weekly due to the size.
    >I would appreciate any information.
    >Thanks
    >
    >Amrita



  12. #12
    Douglas J. Steele Guest

    Re: Access Limit

    That may well be, but why risk it? Check
    http://support.microsoft.com/support.../Q279/3/34.ASP They have the
    same warning in it twice:

    IMPORTANT: Run the Repair Database command only when the Microsoft Jet
    database engine returns a message indicating that the Repair should be run.
    The Repair Database command should not be run under any other circumstances.

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Michael Hodes" <mhodes@earthlink.net> wrote in message
    news:3a81fa5b@news.devx.com...
    >
    > I've setup auto repair and compact utilities to run on some Access 97

    database
    > and the repair, even without prompting, works fine. Some of these

    databases
    > have been in production for a couple of years.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center