What are the limitations of MS Access?（什么是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.

My primary reason, however, is that Access (and the Jet drivers) can only manage a handful of users at any given time.

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):

• ODBC driver version
• database structure
• number of tables queried
• number of queries per user
• size of tables
• number of rows
• number of columns
• size of columns
• use of stored queries
• use / size of indices
• bandwidth
• processor speed
• disk subsystem
• RAM
• hard drive space
• behavior of users

Microsoft doesn't even publish hard numbers; they just use off-hand references such as "ten or fewer" and never truly define what they mean by "high-concurrency."

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.

"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."

"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."

"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).

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.

Aside from the concurrent user and permissions problems, Access lacks many other qualities of a mission-critical, enterprise-level database.

Scalability
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.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.

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.

Integrity
"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.

Replication
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.

Database size
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.

Security
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.

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).

Transactions
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.

Triggers
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 Mail
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 .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.

Jobs
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.

Stored Procedures
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!

Syntax Consistency
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.If you don't discover this problem early on, you may have a lot of code to re-write.

Management Tools
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 and ship with tools that help focus on managing the database without the clutter of application-scope tasks.

Feature Development
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;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...

Finally, if you're stuck with Access, make sure you use the latest version of MDAC,use a JET connection string,and keep your database in good shape.Also, make sure your server has all the latest updates.And I can't express strongly enough how important it is to stress test your application.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.