What's the capacity of ms access 2003?

Question

The limit of an access database. Because I only know of manipulating access 2003 database with vb.net. And were going to make an information system for a small hospital for our project. Is ms access still usable for a hospital with over 2000 patients per year? Could you suggest some database if the ms access that I'm using no longer fits for our project.

Answer 1

My personal favorite is PostgreSQL.

Answer 2

Here is one of the first google hits for limitations in Access. It's not completely clear to me which version that is for. Nonetheless, you can probably find the limits easily for the version of interest with a bit of searching.

In reality, from a storage perspective, Access could likely store the records for 2000 patients a year ... but it doesn't seem like a good idea. It does not handle multi-user environments very well at all, which I assume would be likely in this case.

As far as suggestions, you can get any number of people listing the one they like best. I would recommend Advantage Database Server. But my suggestion could be considered a bit biased since I am one of the developers on it. There are quite a few database engines that will work. You will need to study them and probably make your own choice based on requirements and functionality provided by the database engine.

Edit I probably should have been a bit more clear that I was not singling out Access specifically as having problems. The issue is more with client/server versus non-client/server environments. It is a simple issue. With file-sharing database systems (of which MS Access is a member), the client makes the updates to the files. On a networked system, this introduces potential problems. Even Microsoft recognizes this. In the section of that article titled Additional best practices for network environments, it specifically says that the database can be corrupted if a client is stopped unexpectedly or if a network connection is dropped.

For example, I just now opened northwind.mdb and deleted the first Orders record. This resulted in 19 file writes to the database file. I tested the same thing over the network and counted 19 individual writes across the network. If the connection is dropped in the middle of that, then there is potential for corruption. With a client/server solution such as SQL Server, a record delete would be one single command sent to the server. It either gets there or it doesn't. If it reaches the server, it can be handled "atomically" by the server. With database environments, the ability to make logically consistent updates is critical. A record update often involves multiple file updates. Not only must the record itself be udpated, but there are often index updates (possibly multiple indexes and multiple page writes for each one in a b-tree), transactions, referential integrity, meta-data (e.g., record counts), etc.

Answer 3

MS Access uses the Jet DB engine, which has a lot of limitations and is officially depreciated by Microsoft. If this a single client machine, SQLite would be a better choice. Otherwise something like MySQL/Postgres or any other multi-user DB.

Answer 4

You would think you could find this on Microsoft's WEB site but only MS-Access 2007 comes up. At this site, it says the limits can be found in the help files:

http://www.databasedev.co.uk/access_specifications.html

Have you looked in the MS-Access 2003 help files? Try searching for "limits".

Answer 5

I do not think it's a good idea to use Access for storing possibly confidential data about patients. If you have to regard security issues, better use a full-fledged client/server database. Since you are looking for something as an alternative to MS access, and if your server OS is MS Windows, Microsoft SQL Server may be an option for you. If you want something that is smaller, mature and can run on other OSes, Sybase SQL Anywhere is a good option. I don't suggest Oracle, this is possibly oversized for someone who is looking for an Access replacement.

If you provide more information about number of (simultaneous) users and what kind of application(s) will use the database, me (and others) can give you a better suggestion what DB to use.

Answer 6

@fupsduck and @Mark Wilkins both cite Access Specifications and Limits, which applies to A2000, A2002 and A2003. All of the specifications apply to A97 and A2007 as well, except these (checked against the A97 and A2007 help files article on Access Specifications and added here for completeness):

Access 97

• max file size -- 1GB in Jet 3.x
• Modules -- 1024 (I don't know why later versions of Access limit it to decimal 1000 instead of a binary number)
• table handles -- 2048 in Jet 3.5 SP1 and up, 1024 in original Jet 3.5
• table size -- 1GB
• record size -- 2K in Jet 3.x (no Unicode). This limit is the data page size, since a record can't span two data pages (the Jet 4 data page is 4K).
• Number of ANDs in a WHERE or HAVING clause -- 40
• nested forms or reports -- 3

A few of the items (like characters in a recordsource/rowsource) are not specified in the A97 help file, but I seem to recall that rowsources are more limited in length in A97 than later.

Access 2007

• characters in a password -- 20

The following items are impacted by whether or not your query includes tables with multi-value fields. This is because a MV field is internally implemented as a proper many-to-many join, so any single MV field will use up 2 joins and 2 tables. It's not clear to me if having a table with a MV field in it uses up the resources, or if it uses them up only if you include the MV field in the SELECT statement. Multi-value fields are available only in ACCDB format, so for MDB format in A2007, this caveat does not apply.

• tables in a query -- 32*
• joins in a query -- 16* <= not listed in A2003 help file
• AND operators in a WHERE or HAVING clause -- 99*
• characters in an SQL statement -- 64,000*