What are the limitations of MS Access?
It's no secret: I do not trust Access. I don't think it should be used in a production environment for anything more than a personal website, and an unpopular one at that. If you're building a web site that you expect will be even remotely successful, you're only delaying the inevitable by using Access now. You will eventually be forced to upgrade to SQL Server (or something similar).
I have many reasons for this, most from first- or second-hand experience. I'm not going to get into all of those right now, because it is somewhat biased by my experience, and the type of applications I specialize in (which may vary from the typical web developer).
I've received criticism that this article is "horribly biased" and that if I can't get Access to work in a high-volume system, I must be a "shoddy programmer." Admittedly, much of the *official* information here comes from the horse's own mouth (mainly knowledge base articles admitting Access' weaknesses); however, the whole purpose of the article is to prevent other developers from making the same mistake I did years ago—assume Access would scale. To add some balance to the article, here are some third party opinions on Access:
My primary reason, however, is that Access (and the Jet drivers) can only manage a handful of users at any given time (see KB #154869, KB #299973, and KB #299974 for some background on this).
I've also received criticism from people who expect there to be a hard-lined, cover-all-your-bases, magic number for how many users an Access database can support. Sorry to disappoint you, but there is no such magic number. There are far too many variables involved, such as (in no particular order):
For some people, a handful of concurrent users is fine. If you have a site with a guestbook, and you get a few dozen entries a week, Access should be sufficient. But if you have a site with database-driven navigation, full search functionality and GBs of data flowing each day, you may want to read this article in its entirety before settling on Access.
From KB #222135
"While Microsoft Jet is consciously (and continually) updated with many quality, functional, and performance improvements, it was not intended (or architected) for the high-stress performance required by 24x7 scenarios, ACID transactions, or unlimited users, that is, scenarios where there has to be absolute data integrity or very high concurrency."
"ASP also supports using the Microsoft Jet database engine as a valid data source. The Access ODBC Driver and Microsoft OLE DB Provider for Microsoft Jet are not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as web, commerce, transactional, messaging servers, and so on."
From KB #225048
"First, Microsoft Jet can only handle a limited number of sessions. If your application uses a large number of ADO Data controls, Jet may run out of resources."
From KB #240317
"Microsoft Jet has a read-cache that is updated every PageTimeout milliseconds (default is 5000ms = 5 seconds). It also has a lazy-write mechanism that operates on a separate thread to main processing and thus writes changes to disk asynchronously. These two mechanisms help boost performance, but in certain situations that require high concurrency, they may create problems."
"Jet can support up to 255 concurrent users, but performance of the file-based architecture can prevent its use for many concurrent users. In general, it is best to use Jet for 10 or fewer concurrent users."
If you're going to try Access, you'll need to test your application under stress and heavy load. I would max out at two to three times your expected volume (because your volume will have peaks and valleys). See Article #2319 for a list of tools and services designed to stress test your web site(s).
You're not going to get hard numbers from anyone else that apply specifically to your application. It is YOUR responsibility to benchmark YOUR application and make sure that Access can handle the maximum number of simultaneous users YOU expect (or hope for). Not performing this kind of test is doing a disservice to you, your employer, and your client -- and opening all up to risk of failure.
With all that aside, if you're stuck with Access, here is a list of links to ASPFAQ and KB articles revolving around specific Access error messages. This isn't a completely exhaustive list, but it should serve to be a good starting point.
80040e07 - Data type mismatch in criteria expression.
Article #2289, KB #136059, KB #175258, KB #184948, KB #208425, KB #210244, KB #246570
80040e10 - Too few parameters. Expected <x>.
Article #2128, KB #162980, KB #171850, KB #172898, KB #178070, KB #181209, KB #181832, KB #184233, KB #191619, KB #205972, KB #207586, KB #216425, KB #237994
80040e14 / 80040e37 - The Microsoft Jet Database engine cannot find the table or query 'tablename'. Make sure it exists and that its name is spelled correctly.
Article #2164, KB #184572
80040e14 - Syntax error in <statement or clause>.
Article #2086, KB #181489
80040e4d - Too many client tasks.
80004005 - *
IIS 6.0 hangs when using Access:
Aside from the concurrent user and permissions problems, Access lacks many other qualities of a mission-critical, enterprise-level database.
I don't believe it can be stressed enough that Access will simply not stand up to traffic. I realize it is tough to simulate TRUE load testing in a development environment, but if it can prevent you from launching an inadequate database, it will be worth the trouble. (Again, see Article #2139 for a list of tools). Mission-critical databases should laugh at traffic; database servers should buckle under bandwidth and RAM contsraints before they're ever stopped by the database itself. As an added point, SQL Server has the flexibility of actually using multiple processes on an SMP machine.
See Article #2345 for a comparison of the capacities of Access, SQL Server and MSDE. If you can deal with a 2 GB size limitation and a built-in limitation of 5 concurrent workloads, you will be much better off starting with MSDE than with Access - especially if you plan to upgrade to SQL Server later.
From KB #300216:
"Unlike a file-based database engine, a server-based database engine such as Microsoft SQL Server processes all of the multiple client requests to a database at the server. The server keeps track of these requests in a transaction log. If, for any reason, a request cannot be fulfilled, the server rolls back or does not process the request. This reduces the possibility that the database will be left in an incomplete or corrupted state."
Access doesn't have a good backup scheme; in fact, it doesn't have a backup scheme at all. Being file-based, there are two problems with attempting to back up an Access database: (1) if rows are modified while the backup is being performed, the backup may become corrupt; and, (2) many backup programs won't even touch a file that is in use. Most capable database systems have a variety of configurable backup schemes, and SQL Server is no exception. SQL Server also has comprehensive locking facilities, making it much more difficult to corrupt a backup.
Similarly, it is near impossible to modify an Access database while it is 'live', e.g. while any user has a page opened that is accessing any table within, or if another user has the MDB file open in the Access GUI. You have to copy the database, make your changes, and replace the 'live' version - waiting until nobody is on your site. Not something that can be tolerated in a 24/7 environment.
Finally, Access has a tendency toward corrupting its MDB files. Granite Consulting certainly has a lot of information on corrupt Access databases, and links to information about fixing or recovering them. I have seen corrupt MDB files once or twice, and can only assume from the vast collection of links that this is a common problem.
A decent database system has at least one way to replicate / transfer content from one database, or one server, to another. SQL Server has multiple options for Data Transformation Services (DTS), and can have external tools "plugged in" to perform similar tasks. With versions of Access prior to 2000, it was always right-click, copy .mdb file, paste. Yuck.
Even the most recent versions of Access do not support database files larger than 2GB. This includes not only data but also forms, macros, reports, saved queries, etc. For a comparison with SQL Server, see Article #2345.
Capable database platforms have multiple levels of configurable security, down to the object level. A user can also be permitted across databases and across servers. Through the context of ASP, Access only has the ability to password protect a database on a single file basis, so you can't have custom permissions per query, table or view. (And besides, the "security" here is a misnomer. There are several code samples and products around the Internet, for example Access Key, that can bypass this security blanket.)
Another security concern is the availability of the database file and the content within it. Since Access is a file-based database, and since in many hosting scenarios it is just placed in the web structure somewhere, it is very easy for intruders to find the database file (by guessing its location either manually or through a dictionary attack), or trying to force an error in your application that might yield the location of the file in the error message. If they can find the URL of the database, they can download it and use the data. If they have access to the file system where the database resides, they can easily delete the file, delete all of its data, or corrupt it in other ways.
SQL Server presents a much more robust security mechanism with multiple layers. However, I will say that it is easy to abuse that system (e.g. by leaving the sa password blank, and/or using the sa login as the user ID in your connection strings).
SQL Server is a transactional database. Aside from remote stored procedures, any set of operations within a transaction can be rolled back. With Access, you would have to either (a) use transactions from an external application, e.g. COM+ or MTS; or (b) revert to a previous copy of the database.
In SQL Server, triggers allow you to perform operations in response to certain events without slowing down the calling application. For example, you could have SQL Mail send you an e-mail after every five inserts to the ORDERS table where the order total is greater than $50. With Access, you would have to create a table, store a count for the number of times such an insert occurs, and code the application to send mail at insert time (which slows down the application itself). Not the prettiest solution.
SQL Server supports a native mail format; as long as there is an Exchange Server within reach, you can tell the database to e-mail specific users on certain events... e.g. within a trigger, or when certain database tasks fail. With a custom add-in, you can also use any SMTP server (see Article #2403). With Access, you would have to code this stuff up yourself - assuming you find some way to trap the event(s) in the first place.
SQL Server supports jobs, allowing you to schedule database tasks and have the system execute them automatically (instead of a user having to invoke them). You can schedule jobs to be performed when the CPU is idle, or at certain times during the day. We use this for number-crunching at the end of each day, and for archiving stats throughout the day to keep our 'active' tables as small as possible.
Yes, Access supports stored queries. But IMHO, these are nowhere near as powerful as stored procedures. For one, it is difficult to have stored queries access data from different databases. With stored procedures, this is trivial at worst. SQL Server stored procedures support cursors and temporary tables, both of which are very powerful tools in sorting data and performing queries. T-SQL also supports conditional logic, such as if / then and case blocks, as well as index hints, locking hints and join hints. Most of these things are either extremely cumbersome or simply not possible in the VB-bastardized version of SQL that ships with Access.
Additionally, SQL Server comes with several system and extended stored procedures, which you can plug into your existing logic to do all sorts of things (such as retrieve a directory file listing (exec xp_cmdshell 'dir c:\'), list all of the users currently accessing your database (exec sp_who2), or iterate through all DSNs on the server (exec xp_enumdsn). Try and do those things from Access!
Many Access developers are confused when they create a stored query that works fine within the Access environment, but does not work from ASP or VB. For example, the function NZ() only works within Access. If you try to use it from ASP or VB, you get an error that the function is not supported (see Article #2394). If you don't discover this problem early on, you may have a lot of code to re-write.
There is no question that the Access user interface can leave a lot to be desired in terms of managing your database. Many people have suggested that the interface is very "Mickey Mouse." Others have felt forced into developing their forms because Access makes it seem like the application and database are tightly coupled. Most RDBMS systems, on the other hand, have a wide variety of management applications available (a list for MSDE / SQL Server is presented in Article #2442) and ship with tools that help focus on managing the database without the clutter of application-scope tasks.
As stated in the SQL Server 2005 Express Edition Overview, Jet is in service pack and maintenance mode. This means the Jet engine and its connectivity components will not have new features developed over time...
Again, if you want to use Access for your personal photos page or your CD collection, and you're not going to publish it for the world to see, then Access is more than capable. I strongly recommend not using Access in any application for which a 3rd party is relying on you, especially an e-commerce or other 24/7 operation.
And I'm not saying you have to use Microsoft's SQL Server, or even the latest version (I prefer 2000 over 7.0). While it is the database that is the natural 'next step' - and many upsizing tools and tutorials are available (see Article #2182); there are several other database packages you can look at, each with their own strengths and weaknesses. My preference, as you might guess, is SQL Server... but I do have some level of faith in all of the products listed in Article #2426. Another article that might be of use is When to Migrate from Microsoft Access to....
For information on upsizing from Access to SQL Server, see Article #2182 and How to Migrate from Access to SQL Server....
Finally, if you're stuck with Access, make sure you use the latest version of MDAC (MDAC Download Page), use a JET connection string (see Article #2126 for connection strings and Article #2342 for JET downloads), and keep your database in good shape (see KB #300216 and KB #303528 — HOW TO: Keep a Jet 4.0 Database in Top Working Condition). Also, make sure your server has all the latest updates (see Article #2151). And I can't express strongly enough how important it is to stress test your application (see Article #2319). Just because it works well for you and your cubemate on your workstation does not mean it will stand up to real-world workloads in a production environment.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?