MS Access limitations

[ditrex]

I need reliable information on the pratical limts of Access apllications. We are deciding the whether to upgrade our Access 2000 apllications to Access 2002 or build it in VB.NET with MySQL or SQL Server.

I know about the maximum connections and dabase size. What I really am looking for is indications of when to transfer to database technologies like SQL Server, mySQL or Oracle.
Can anyone provide me with information or a link to information about these practical limitations?

When I for instance say that performance on a database with 15-20 concurrent users (editing the same tables) slows down dramatically, I can't back this information with any source, because officially there can be 25 connections to a database?!

 

[evalesthy]

Do an 'Advanced Search' in this forum using "access limitations" (all words setting) for all time periods and you will find many discussions of this topic. In the end what you will find is no consensus of opinion. Bear in mind that there are significant issues like how robust the product is, how fast it performs, ability to roll back transactions, etc. etc. which all should be considered. I think you can also find some white papers on this topic on Microsoft's Knowledge Base.

 

[ditrex]

Thanks for the Tip and your reply. Before I posted this I tried to find and read some discussions about it but the most results handle other issues. I will try again.

Do you think the information supplied by MS will be practical? The information I found so far only tells the physical limits?

 

[evalesthy]

I can't find the MS White Paper I was thinking of. Here is another overview of the topic.

http://www.aldex.co.uk/sqlserver.html

Try some other web searches and you will probably come up with something. If you find something good post a link back here.

 

[Hap007]

Dear ditrex,

There are no real limits on an Access Database (Within reason, yes there are limits, but very few companies ever attain those limits. Performance usually makes them move before the limits are reached)

In theory, you can have gigabytes of storage.
I have an application that has an Access backend that has 20 users connected, the database size is almost 1 gig and all runs fine.

I have another Access app that has almost 100 users connected all day. Also runs fine.

In theory, you can have 255 users attached to one database.

When performance begins to slow down or the number of active users grows, well then it is probably time to consider moving from file server (Frontend Access program with Backend Access database) to a client server (Frontend Access program with a SQL Server database).

Access provides nice tools to upsize you application.

So I would suggest keeping your investment in your Access Program while utilizing SQL Server performance.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[psemianonymous]

I am always pimping Google Groups, if only because searches literally take less than a second:

http://groups.google.com/groups?q="...8&group=comp.databases.ms-access&start=0&sa=N

The help files have specific programmatical limitations:
Database specifications

Attribute Maximum
Database (.mdb) file size 1 gigabyte. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1024
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255





Little side note: I can personally verify the 1GB limitation--it's a programmatical limit. The database will give you an error if you try to add more data to a big database, and it will store up to exactly 1 gig.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[bigjohn3k]

If I upsize my Access database to SQLserver, is the concurrent limitation of 255 users stay the same?

 

[psemianonymous]

255 users per MDB. Assuming you use JET security (which you don't have to anymore since you're using SQL Server), you *probably* will max out at 255 users per file. So technically you could duplicate your front-end file and workgroup file so that more than 255 users can use the database.

If you aren't using JET security, you can simply give everyone their own copy of the Access front-end file. Jeremy Wallace (of these forums) has an easy and excellent way to auto-update these distributed front-end files, and an example is provided on his web site:

http://www.abcdataworks.com/Developers/Deploy.html

http://www.abcdataworks.com/Developers/Batch.html

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[bmd2k3]

Looking for feedback on Web enabled Access db's.

Size / performance issues etc in a web environment.


Thanks...
-Brian

 

[BNPMike]

There is no easy way to judge how complex a database application is. Think of google etc supporting millions of transactions per day. Then look at some applications I've seen with complex SQL, transaction control and heavy update on the same data. I recall an application supporting maybe 200 users but needing 16 mainframe partitions.

In my experience most performance issues are caused by sub-optimal programming or trying to create over-complex solutions to business functions.

Access is good for simple RAD applications. If your application is getting too complicated then probably a more mainstream platform would be better.

Nobody got fired for choosing ASP.