Read and write from and to single Ms Access database from many applications simultaneously

I've tested this and I can see is possible to have multiple opened OleDbConnections from multiple applications (or same many instances of the same application) to the same Access database, and even have many OleDbDataReader opened at the same time.

But is this safe? Should I t开发者_运维技巧ake any special care or will the Jet engine take care of everything? What would happen if I'm reading one data table while writing to the same table at the same time?

I just don't want to stumble upon gotchas or surprises later on.

Access is generally not the engine for concurrent data activity. Any reason why you would avoid MySQL or Oracle or other proper RDBMS?

Some reasons:

-You are not guaranteed a read consistent view.
-You get the strong possibility of updating an updated record without knowing.
-You may experience unresolvable locks
-You have no centralized transaction manager

Access was not designed for concurrent usage. You should investigate using a different database. Here's an excellent article on the subject.

To directly response to your question What would happen if I'm reading one data table while writing to the same table at the same time?

Depends on the default IsolationLevel. You can read about what Isolation Levels are and how to specify them at Protecting Your Data Integrity with Transactions in ADO.Net.

But as others suggested, please use something else. SQL Server 2008 Express is fully featured and mostly free. If you still want the ability to use a database without installing a server software I would even recommend SQL Server Compact Edition over Access.

My personal experience is, for 5 to 10 users (each one with 1 connection) with "not more data a user can enter by typing some input into a form", used in a local network, Access is ok as a backend. This article reflects my personal experience:

http://www.tek-tips.com/faqs.cfm?fid=4462

The real limits and the point where you should look for a "real" Client/Server database is heavily dependent of the details of your environment - if you want to know, you will have to run your own benchmarks.

Concerning the concurrent read/writes to a table: Access uses the byte-range locking capabilities of the underlying file system to deal with collisions (see here http://en.wikipedia.org/wiki/File_locking for more information). This mechanism works on most modern file systems like NTFS, but does not scale well to many users.