3

We have an MS Access database (accdb) out on our network, that multiple users will edit & read by means of a .NET application. I am aware that a server db such as SQL Server would be the best choice for this situation, but currently that's out of my hands.

The .Net application will use both ADO.Net (ie OleDBConnections) and the tools inside the Microsoft.Office.Interop.Access.Dao namespace to connect to the database.

I have read a few articles and posts about multiple users connecting to Access, and there seems to be some confusion and differing opinions about Access's capabilities in this regard. How/can I achieve the following in my application:

  1. Establish connections to write to the database, that will lock the entire database (all records and tables) until the connection is ended. If other users attempting to write simultaneously are halted by an exception, that is okay.
  2. Establish connections designated as read-only, that have no conflicts with any other user's actions.

Thanks.

Tekito
  • 840
  • 8
  • 24
  • 5
    Well as I said right in the first paragraph, I know SQL Server is better but it's not my choice. I also made clear I've done a search and there are varying opinions on Access and multi-tenancy. Not the best comment. – Tekito Dec 05 '14 at 22:58

1 Answers1

7

To open a ACCDB in exclusive mode you need to add this key/value to your connection string

Mode=Share Exclusive;

This will block other user to connect to the same database until you close and dispose the connection that opens the database in this way.

If I remember well, the possible values (pretty self explanatory) for the Mode keyword in the JET connection string are

Mode='Read'; 
Mode='Write'; 
Mode='ReadWrite'; 
Mode='Share Deny None'; 
Mode='Share Deny Read'; 
Mode='Share Deny Write'; 
Mode='Share Exclusive';

I have tried various combination of the flags above, but I can't find a simple solution that allows a single connection to be opened in ReadWrite while the following connections fall back to Read Only automatically. Probably in your scenario the best path is to have a local connection (not a global one), try to open it in Share Exclusive if you need to write to the database and catch the exception if you cannot open the database giving the user a Retry option. Not an easy path I know. Let's see if a user with a better knowledge of MS-Access could give a more elaborate solution.

I agree with the comment above (and your own assesment) that this is not the best database tool to use in situations of concurrency, however things are a lot better now.
I find this answer well written, comprensive and with a balanced view of the strength and weakness of Access about concurrency issues.

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks I will try this next week, 5 pm here :). I had come across that link myself, but the in the section `3) Locks and .NET` he seems very unsure if there is a way to lock using `Oledb`... – Tekito Dec 05 '14 at 23:05
  • `Share Exclusive` locks everything. Tested with a sample program and trying to open the same db using Access and I get the _File in Use_ error message. – Steve Dec 05 '14 at 23:47
  • Thanks, I will mark answer if this works next chance I get (Monday). Is there a way to still allow concurrent connections that are only reading data? If not, this solution is probably still good enough... obviously dealing with a crude setup. – Tekito Dec 06 '14 at 03:41
  • Exlusive seems to work, thanks. And the additional modes will probably solve my desire for permitting read-only connections, without need for try-retry... I probably wasn't clear enough on that part. – Tekito Dec 08 '14 at 16:48