Access limits and corruption

[W01fgang]

What is the limit of Access database everyone has experienced?

Observation of various small projects based on MS Access Db at work, records above 200,000 or 50MB seems to be more prone to corruption.

what is the prudent limit (not the theoretical one) everyone keeps their access database within vis-a-vis record numbers?

[RobDog888]

That seems about right. There is also a limit of around 7 - 10
concurrent connections before the corruption begins. This is a
know issue of access.

You could use SQL server if available. If not try the MSDE instead.
Middle of the road for your size database.

[mendhak]

Access should be used for small scale apps. As a rule of thumb (I have a big thumb with a centimeter scale on it), max size: 5 MB, max users: 5 concurrent.

Beyond that, I use MySQL.

[nazeem_khan]

Try using MySQL server, it is free.....

[sw_is_great]

Access - max 12 concurrent connections
Db size - max 10MB

please note it is 12 concurrent connections and not users.. one user may open mor than 1 connections.


Thats why I always say go for Oracle. (people -- donnt say go for sql server)

[mendhak]

SQL Server > Oracle, any given day.

[sw_is_great]

In which prospect

SQL Server > Oracle, any given day.

[mendhak]

Well, you can take a look at the sticky thread at the top of this forum for that.

[default user]

we're up to 100mb, 50 active connections (1 per user, per app), on DAO. No corruptions. Its as fast with 1 user as 50. Couple tables are over 250000 records.

It appears the limit is still far beyond those stats.

Its all how you implement the system, DB design and test for optimal multi-user performance.

[W01fgang]

Originally posted by default user
we're up to 100mb, 50 active connections (1 per user, per app), on DAO. No corruptions. Its as fast with 1 user as 50. Couple tables are over 250000 records.

It appears the limit is still far beyond those stats.

Its all how you implement the system, DB design and test for optimal multi-user performance.

That's interesting. Please describe more on how as many as 50 users connect to Access (97, 2000 or 2002?) Db, reading and writing. What are your main considerations in making that works smoothly. That is something i have failed a few times.

I am assuming a 50 users network via a client-server arrangement. Are these connection concurrent?

There were a few projects that I worked on that has large number of records. Eg. single table Db with 7 fields around 300,000 - 450,000 records. It is quite often but not always that around these numbers (sometimes before or after) we usually find:

1. Odd mixed up of fields
2. Corrupted Db, unable to read or write.

Corrupted Db could often be repaired and compacted just find after a few tries, but there will certainly be data lost on the oldest group (or earliest) records.

If i have to use Access due to dev. budget constraint, the techniques i used often is to to move older record group into an archiving Db and keeping the more recent records in active connection. Thus maintaining a smaller active Db.

Oh, i know it is way more reliable to work with MS SQL when dealing large record numbers. There are just some jobs that don't have that kind of budget. Well MSDE has certain artificial limitations builtin for large number of concurrent connections.

I hope to get an understanding how far Access can go and be managed without cracking up.

[default user]

'97 and '00

Yes they are concurrent.

90% read, 10% write, lots of I/O.

Ths is just the basics, but

Only use datacontrols when a grid is required.

Use long integer (or autonumber) for your main keys.
Related tables should be simlilarly indexed.

Limit size of snapshot Rs, else use a dynaset. But use dynasets sparingly. However Move first/last causes the dynaset to fill, taking longer then a snapshot anyway.

Only show read/write data when probability of editing is very high. Else show snaps, and open another dynaset to write momentarily, refresh the snap.

Don't use stored (built-in) queries for often used functions. While they may EXECUTE faster, Only ONE user can access the query at a time <gag>. Just pass an SQL statement.

Leave your users logged in (read, keep a public Db object active)
The DB handles multiuser just fine, the ldb can only be opened for write access one at a time, so users stack up and wait, or fail.

Access Relationships in the DB contribute heavily to bloat. Can them.

Fix the oppertunistic lock problem on Win2k machines and/or server according to MS instructions.

Make sure all the users are running the same, most recent jet version (check upon app startup)

READ read read, reasearch, then TEST TEST TEST techniques in a multiuser environment.

After DAO is optimized, ADO is 10-15 times slower than DAO for most Read/write/append operations.

[W01fgang]

What you mentioned sounds good, and will keep them in mind and find sometime to test out the ideas.

much much much and much appreciation.

does anyone know any exact cause for access Db corruption? Anything that should be avoided in coding multi-user apps?

[bebandit]

That info you just gave is awesome!!! Thanks.

I am looking to do a large project with Access and those numbers and requirements are promising. Thanks a ton!!

I think Access has surely improved with time and its earlier limits are disappearing.

[Phill64]

hi, on a similar note... does anybody know the actual record limit when using autonumber?

is this in the billions? millions?

i ask because, if there is a limit, i would be using a text field, using 1-0 and a-z representing numbers, which would allow for 36^255 however tricky to implement while keeping speed up (due to duplication issues needing to be handled) and the last used id would be stored in one place that all places would be trying to access at once etc...

or is there a limit on records anyway, even without autonumber?

[RobDog888]

Here is a link with all the specs.

http://www.microsoft-accesssolutions...ifications.htm

Seems its a size limit and not a row number limit.

[Phill64]

Thanks alot.

[RobDog888]

Although the size limits are unrealistic because it just get soooo slooow when it reaches a couple of hundred Mbs in size. I have one thats 636 Mbs with 6 tables, 3 of which have ~2 million rows each. It takes ages to open the table and I dont even want to take about doing a compact and repair on the db! We are talking ~ 1 hour!

[Phill64]

woa, that's awhile. Hopefully you do not have to perform one very often.

however, databases intended for this size are typically not supposed to be opened in Access itself, not in table view anyway, you would never be able to pinpoint anything anyway

it comforts me to know that you have a table supporting 2mil's worth, sort of proves these rumours wrong (ones about access not being capable of large scale)

[RobDog888]

Well thats the misconception. Its total db size thats the limitation. It takes a combination of either a few very large tables or allot of db objects to reach the limits.

No, I dont have to do too much to that db only about once a month and the data gets imported via code but I need to do the C&R on it manually.

[Hack]

it comforts me to know that you have a table supporting 2mil's worth, sort of proves these rumours wrong (ones about access not being capable of large scale)

In terms of acceptable production performance, those rumors are absolutely true. It is functionally useless to be able to store two million records if it takes two million years to search or gather a recordset based on a query.

[RobDog888]

MS SQL Server 100%