Developing a solution using Microsoft Ac

Although not a full-scale enterprise RDBMS like Oracle, Progress, Sybase, SQL Server, etc. MS Access is a full RDBMS that supports multiple users. Its classification is more of a "desktop" RDBMS, but it has more capabilities than other desktop systems, like the xBase systems, or using the database facilities of Excel.

Here are some stats that are relevant to your question of multi-user limits:

- Max. of 255 concurrent users. In contrast, the documentation of the 2 other RDBMS's I regularly use, SQL Server and Progress, lists their numbers in the thousands, effectively being limited by hardware constraints.

- Max. of 2,048 open tables. But this includes internal system tables, so the actual number available to users is somewhat lower.

- 2GB max. DB size. Again, in contrast to full RDBMS's which list their limits in Terabytes, but 2GB should be more than enough for the overwhelming majority of applications. Also, SQL Server is also limited to 2GB when using the Desktop Engine.

To sum up, although you didn't mention the specifics of your requirements, you should probably find MS Access suitable for your needs. However, I would recommend that you look into using something like SQL Server or Progress, not just forthe DB limits, but more for their superior handling of serious DB requirements such as crash recovery, multitasking, etc.

Thank you for the reply. You specified 255 concurrent users. Is it concurrent users for read as well as for write access?
During my first experience, I had a problem with concurrent users opening the same table for write access? What did I miss?

It should be OK by default, but what you want to make sure is that under Tools->Options->Advanced you have the "Shared" option checked (not the "Exclusive") under "default open mode". Also by default, records should not be locked (same option tab)

Yes, the 255 is for write as well as read access. However, the problems you had with simultaneous writing are not specific to MS Access, but are problems you will encounter in any multi-user application where there are shared resources. It doesn'teven have to be an RDBMS, even a real-time process application with a shared resource like a semaphore to coordinate multiple users can encounter the same problems.

The main problem is record locking. To update a record you must have a lock on it. The trouble is, while it is locked any other users trying to access it will be put on hold. This is further complicated by the fact that some DB systems don't actually lock individual records, but lock groups of records in a block. The trick isto minimize the time spent locking a record, e.g., at the point when you're actually about to save updates to the file, instead of during editing. However, this can lead to problems of its own, such as when someone else edits or deletes a record you're editing.

The topic of multi-user programming is a long and complex one, beyond the scope of this forum. I recommend that you read RDBMS books; the ones by C.J. Date are excellent. I hope my advice doesn't come across as patronizing but I don't have any details of the problem, nor of your background and the extent of your DB development experience. If you're a veteran, simply interpret my recommendation that you read RDBMS books, not as a suggestion to "learn", but as a suggestion to review.