Max num of Access records

[litton1]

hi all,
I was wondering what people’s opinions where on the maximum number of records that Access can handle before noticeably slowing down.

The reason I ask is that the Access database that I am working with at the moment has over 30,000 records in its largest table and is slowing down a bit, hence we are wondering if we should streamline it or change database systems?

Thoughts appreciated.

Binary Intelligence, true or false?

 

[alvechurchdata]

Our back end mdb is 40Mb after compaction and the biggest table is 150,000 records. We have however put a lot of effort into tuning the code and queries.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[smedvid]

Just some quick thoughts.... Certianly, 30,000 rows is not large; however if you have 150 fields in the table and several indexes, there could be some performance issues in MS Access as the data set grows. If you do not have indicies, add them to insure that all queries are optimized. This will improve performance. Any field used in a WHERE condition is a general rule to have an index on.

How many users are hitting the MS Access DB? Do you have the Front End and Back ENd database split? U can't go wronf upsizing to MS SQL Server if the application is for the enterprise, and requires robust management/security/multi-users/backups... hthw,



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SeeThru]

I've got one talbe with 400K records. It only has 4 fields, and is used for a lookup only, so is pretty good.

Importing the new data sets (complete replacement each time) and compacting take a while though!

 

[smedvid]

FYI: I typically have users export a table with 1.7 million rows and 70 columns. The only issue is the MDB File Size is huge ~1.5 GB. The maximum file size of MS Access is 1.98 GB (Just shy of 2 GB). I know this from experience.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[warthog72]

30k records should be a walk-in-the-park for any version of Access better than 97 (unless like previously stated, you have 100+ fields per record).

You really shouldn't be seeing a drop in performance at that size; you can see others have larger - mine particulary sits around 120mb after compacting. What is actually slowing down? Is it query execution, record browsing, record editing?