What is to much

[gdspeare]

How many records can an Access table hold and still work relatively efficiently? In other words, at what point should you consider upgrading to a more powerful database program?

 

[Norie]

I would think that it depends what you are actually doing with your data, how you have it structured and where the database is located - network drive or local drive.

I've recently been working with >200,000 records and it became quite unwieldy and slow when using it on the network.

When transferred to the local drive (C then everything (queries, reports etc) speeded up considerably.

I think in the Access help files a size limit of about 1GB is mentioned when talking about upsizing.

 

[Andrew Fergus]

I agree with Norie - Access databases work much better when based locally and yes the limit is almost 1Gb per table for Access 2k and apparently 2Gb for Access XP & 2003. How many records you can fit into the main table will depend on how efficiently you structure your data. Although I have noticed Access chokes once you get to about 1m+ records. You can get around this limitation by archiving old records but retaining high level summary data in the main table for reporting purposes.
Andrew

 

[Corticus]

Yes, I believe 1 Gb is the upper file size limit before you need to upsize to an adp (which has a limit in the terrabytes). As andrew pointed out, your rganization can make a huge difference in performance. I have a couple dbs with millions of records and they do fine, just be efficient. Also, don't forget about Tools|Database Utilities|Compact and Repair. Thas can GREATLY reduce the size of your file, and thus improve it's performance.

Also, keep on top of your data types (and field sizes - a field for a middle initial does not need to be 50 characters, for example). Finally, index foreign keys when possible.

Also, as andrew mentioned, performance across a network will be way slower.