Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is 770,000 records too many? (Access 2000)

    I've got a db which is constantly getting corrupted (like every day). One of the tables now has 770,000 records and is growing. I have a pretty old computer. Do you think I should get new hardware, re-design the database so, somehow, there are fewer records, or try a different product ... SQL Server maybe???
    Thanks,
    Gwenda

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Is 770,000 records too many? (Access 2000)

    There is no definitive answer. There are alot of factors: # of users, how much use is is giving the system, what kind of activity (that is, alot of deletes/adds, mostly adds/changes, or what), etc.

    However 770,000 records in a single table is alot of records, and your performance would probably be better by migrating to an real client-server database (like SQL server). I'd say it is something you should be considering.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Is 770,000 records too many? (Access 2000)

    You are stretching the limits of Access 2k with a 770,000 record table, but we have done that in the past. The real question is how big is the .mdb file? You may be bumping up against that limit, as 2k has a nasty habit of ballooning, especially if you are creating temporary tables or objects. Other questions that are pertinent to your problem:
    <UL><LI>How many users are accessing the database?
    <LI>Is it split into a front-end and a back-end?
    <LI>Are you using memo fields in the large table?[/list]SQL Server will handle that size database and much larger, but you still need Access or some other product to provide forms, reports, etc. You could consider using an ADP, or you could archive older records to another database and simply attach to it if/when you need to see the older records. Hope this provides some insight!
    Wendell

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is 770,000 records too many? (Access 2000)

    Hi Wendell,
    Thank you for your help. What is ADP?
    Gwenda

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Is 770,000 records too many? (Access 2000)

    ADP stands for Access Data Project, and is one of the kind of database you can create with Access 2000/2002. It is intended to serve as a front-end to what is know as the MSDE or Microsoft Data Engine (which is really a crippled version of SQL Server 7.0), and with SQL Server. It works rather differently than Access databases which use a Jet (.mdb) file, as you connect directly to SQL Server or MSDE, instead of using ODBC connections. It gives you better performance on average than an Access back-end, but the development process is more complicated, as you work with views instead of queries, and you have to use Stored Procedures instead of Action queries. In 2000 the tools aren't as robust as for .mdb files; they've gotten somewhat better in 2002, but I still find the .mdb files easier to develop in. Microsoft has stated that this is the long term focus for Access, and that no more significant development effort will expended on the Jet side of things.

    This is probably way more than you really wanted to know, so let me summarize. Unless you really want to dig into SQL Server, etc, I would stick with a .mdb front-end and use ODBC connections to SQL Server, assuming that's the path you choose. Otherwise, I would probably create a separate database with just the archived records for your large table. You can still link to it and look if you need to, but there's no point in burdening your main database with old records if you don't have to.
    Wendell

Posting Permissions

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