1

In my application, one user at a time is supposed to work with an Access DB file for the entire "session" (time span he has the application open). The file shall be locked while one user is working with it.

I do so by using lock according to: How to lock a file with C#?

FileStream s2 = new FileStream(name, FileMode.Open, FileAccess.Read, FileShare.None);

However when accessing the file via code, an exception is thrown, e.g.:

// Connection points to file locked before
using (OleDbConnection connection = new OleDbConnection(Connection)) {
connection.Open();
//...

System.Data.OleDb.OleDbException File is already being used

How to access previously locked file? Do I always have to remove the lock before accessing it? If so, how to ensure the file is not being accessed inbetween unlocking, accessing and relocking?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ray
  • 149
  • 1
  • 4
  • 18
  • Oledb, Access, Excel sharing of files do not work properly. It is recommended not to share any application bases on oledb. – jdweng Jun 03 '16 at 10:12
  • Is there any solution in this case tho? As right now, I can only work with Access and have no other possibilities to switch to. Right now I create a Lock-File on my own and check before, whether this file exists, however this is really horrible. – ray Jun 03 '16 at 10:16
  • 1
    Simply lock another file. If you have `some.mdb` create and open exclusively `some.mdb.lock` before accessing it. – Sinatr Jun 03 '16 at 10:21
  • @jdweng Btw is the actual reason behind my question indeed that I wanna **prevent** sharing of those files. – ray Jun 03 '16 at 10:22
  • @Sinatr then someone can still just open some.mdb with any other program and I want to prevent that as well. Furthermore I'd need to manage my own lock file, which I already do at the moment. However I'd like to get away from that. – ray Jun 03 '16 at 10:25
  • 1
    The point is to synchronize access to that database file using another file (*file-based synchronization* of access). Only after successfully locking that file you allow that user to access database. That's the simplest way I can think of (disregards if database supports multiple connections or not this will work). Other options are server/client architecture, where you don't access database directly, but via some server, which will handle all synchronization requests and more. *"just open some.mdb with any other program"* - you can't prevent this if file can be accessed directly. – Sinatr Jun 03 '16 at 10:25
  • You could move the file to another location. Then move it back when you are done. – jdweng Jun 03 '16 at 10:29
  • @Sinatr Take Office products, lets say Word, as an example, what exactly do they do? When opening a word document, they seem to manage both, locking the file for any other application and letting the actual user who opened the file work on it within Word. That's pretty much the actual functionality I'd like to have – ray Jun 03 '16 at 10:33
  • Make the file read-only which will force people to make changes in their own copy of the file. – jdweng Jun 03 '16 at 10:34
  • I think your connection must be Exclusive: http://www.connectionstrings.com/access/ , must open connection in Mode Exclusive, do something, and close connection – Raspberryano Jun 03 '16 at 10:35
  • @jdweng if you mean the read-only tag within windows: I'm pretty sure when executing code, this would come up as an exception as well (just as it does right now, when locking the file). (Also, people could simply remove this tag, however I wouldn't see that as an issue if it worked) – ray Jun 03 '16 at 10:38
  • What do you guys think about keeping the OleDB connection open the entire time my application is open (referring to @Raspberryano 's idea)? Not best practice, but would it do? Any issues? – ray Jun 03 '16 at 10:42

2 Answers2

1

I think your connection must be Exclusive: http://www.connectionstrings.com/access/

Raspberryano
  • 159
  • 1
  • 10
  • No one is actually working with the Access Application. It won't help in my case, as I want to have the file locked for the entire "session", meaning the whole time the user has my application open and selected to work with a specific access database. – ray Jun 03 '16 at 10:28
  • Ok, I change my comment, please read me. You must open connection in mode Exclusive, do something, and close connection – Raspberryano Jun 03 '16 at 10:29
  • Quickly tested it and unfortunately didn't work either. I think we're talking about 2 different things here. I have a file (Access database) which my application accesses from time to time. During the time, the user has opened my application and selected this file, I want to prevent anyone else from accessing the file in any way - whether by using my application or another application. – ray Jun 03 '16 at 10:36
  • What Access version are you using? – Raspberryano Jun 03 '16 at 10:39
  • Access 2013. Anyways, I thought again about your comment. One possible solution would be to have a permanent connection to the database during the whole time, my application is open. Probably not best practice but it should work. – ray Jun 03 '16 at 10:41
  • It is I said, open your connection in Exclusive mode and don't close until you want it. I don't find another alternative to your requeriments – Raspberryano Jun 03 '16 at 10:41
  • 1
    @ray, don't have permanent connection. Instead open exlusively (I thought this is clear) **another file**. Everybody who want to connect to database have to first try to open that file exclusively, similar to `lock()` where variable is this file. Once able to exclusively open this file - only then allow to actually access database (this time it doesn't matter how you open it), after you finish - close that file. You have to *acquire lock* (open exclusively) every time, but the point is what while your application running and not accessing database then the others can also access it. – Sinatr Jun 06 '16 at 06:59
1

The solution is indeed to create a separate lock file and lock this using a file stream.

(The answer is actually given by Sinatr, just for the sake of completeness I want to share it. Yet to find out is what MS Office does to its office files, as that is the exact behavior I'd like to have - but that is a different question.)

ray
  • 149
  • 1
  • 4
  • 18