Microsoft Access Database, Design Limitations

[grogk]

What do most database specialists reccomend as a maximum number of records that should be contained in an Access Database before an enterprise solution like SQL, Oracle or InterBase should be considered? Is 18,000 records way too large? Thanks for your thoughts.

Grogk

 

[Craig0201]

Nope!!!

Look in Access Help for limitations re: size.

"Industrial quality" solutions are as much about multi-users, security, extra features, etc as size of data.

Craig

 

[svanels]

If these 18.000 records are made up of only 2 fields it is a different story then if every record contains 25 fields.
What about the rest of the Database, how many tables? Security, multi-user, orphan fields or tables, data {in)consistency?

Steven van Els
SAvanEls@cq-link.sr

 

[zemp]

Access really starts to slow down when it reaches about 2 gigs of data or more than 5 concurrent users. When these limits are reached it may be time to consider an enterprise database.

Another solution, that I have heard of but not used, is to split the data into multiple Access databases. This keeps each database small and fast. However there is more coding and maintanence involved with this method.

Thanks and Good Luck!

zemp

 

[dvannoy]

Access is great for small DB's and as Zemp said under 5 users. can you have more then 5 users connected at once? of course. I am running a DB with 12 tables each contains about 55,000 records about 20-30 fields in each DB and about 25-30 users hit it each day, all day long. But I am in the middle of switching it to SQL server. you will find the more data and users you have, the more your DB will get corrupt. You will also have to babysit access with large DB's.

 

[psemianonymous]

Blah. Access has the ability to support many concurrent users, provided you don't use many of the features built into Access (such as bound forms or too much reporting). Depending on how intensive your database usage is (usage volume?), your application can support well over twenty concurrent users. With replication you can have users enter data into several separate databases and merge the data periodically, though this means that until you synchronize, the data may be stale.

In most cases, though, it is probably more cost effective to upgrade to a database server.


As for size, depending on which version of Access you are using, Access supports up to 1GB or 2GB of data. The amount of data is not so much a problem as is how much of the data is being accessed at any given time. So what I'm saying is that your database speed will NOT decrease exponentially with the size of the database, but it will help add to the limit of your file server's ability to grab data from the Access MDB file and send that data over the LAN. At some point your file server/limits set on the MDB file will be swamped, and this is when database corruption begins to frequently occur and the speed slows to a crawl. This is the reason why it is highly recommended that you disable opportunistic locking for Access databases--so that multi-user interferences such as described above do not occur.

 

[svanels]

Depends of what is in the database.
if it is a cook-book or an adress-book, that will be allright.
I can't imagine someone pooking around in an adressbook 8-hours a day. another story is when it is comercial application. Imagine a big supermarket with about 20-30 cashiers/computers linked to the database for inventory control, financial transactions, orders etc.

Steven van Els
SAvanEls@cq-link.sr