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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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:

• How many users are accessing the database?
• Is it split into a front-end and a back-end?
• 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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote