2

I have an incredibly simple Access database. It has one pass-through query and that's it (no tables). The query simply executes a stored procedure against a SQL Server database to display the results. There won't be any data saved in the Access database; it will only be used to display the results of stored procedures.

I put the database on a network share. If I have the Access database open, non of my co-workers are able to open the database to view the data. They get the error: "Could not lock file".

How can I allow multiple users to open the Access file and view the query results?

2

How can I allow multiple users to open an access database on a network share?

Share a database by using a network folder

The simplest way to share a database is to put it on a shared network folder. Although this is the simplest method, it is also the most limited. Before you consider this method, all the following conditions should be met:

  • No more than a few people are expected to use the database at the same time.

  • No Memo fields are present in the database, or if they are, they will not be simultaneously updated by different users.

  • Users do not need to customize the design of the database.

Note This method is less secure than other methods of sharing a database, because each user has a full copy of the database file, increasing the risk of unauthorized access.

To share a database by using a network folder

  1. If one is not already available, set up a shared network folder.

    For help with this step, see the help system for the operating system of the computer that you want to use to share your database. If the shared folder is on a network server, you might need assistance from the network administrator.

  2. Make sure that Access is set to open in shared mode on all of the users' computers. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with others' use of the data. Perform the following procedure on each computer:

    • Start Access.

    • Click the Microsoft Office Button Office button image, and then click Access Options.

    • In the left pane of the Access Options dialog box, click Advanced.

    • In the right pane of the Access Options dialog box, in the Advanced section, under Default open mode, select Shared.

    • Click OK, and then exit Access.

  3. Copy the database file to the shared folder. After you copy the file, make sure that the file attributes are set to allow read/write access to the database file. Users must have read/write access to use the database.

  4. On each user's computer, create a shortcut to the database file.

    When you enter the path to the database file in the Target property of the shortcut, use a UNC address instead of a mapped drive letter. For example, instead of F:\sample.accdb, use \computername\shared.accdb.

    Note This step can also be performed by the users themselves.

Source Ways to share an Access database

  • @DavidPostill - Like I mentioned in my post, I have the database in a network share. Only one person is allowed to open the database at a given time. They get the error: "Could not lock file" – Justin Helgerson Oct 27 '15 at 19:28
  • @Ek0nomik Then you are not opening the database in shared mode. – DavidPostill Oct 27 '15 at 19:38
  • @DavidPostill - It seems if I (the creator of the Access database) have it open then nobody else can open it (even though I have my client settings set to open in shared mode). If someone else has it open then I am still able to open it. – Justin Helgerson Oct 27 '15 at 20:58
  • @Ramhound - I'm still having an issue with locking. I have the .accdb file on a network share. If other users open the database it seems okay. But, if I open the database a .laccdb file is produced and the Access database is locked. My settings are to open in shared mode by default. Why is it locking when I open it (I was the creator of the file)? – Justin Helgerson Nov 2 '15 at 16:39
0

If anyone is asking this question but using OLEDB, it seems "shared mode" is always on due to the existence of an"exclusive" mode:

Exclusive Used to get exclusive access to the database if you, for instance, want to let the application be able to reset the database password.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb; Mode=Share Exclusive;User Id=admin;Password=;

https://www.connectionstrings.com/access/

Also this thread has some intersting info about using "mode" https://social.msdn.microsoft.com/Forums/en-US/c90b1166-e5ee-43ff-a49b-9efe9f416475/opening-an-ms-access-database-in-exclusive-mode-using-adonet?forum=adodotnetdataproviders

protected by Community Jan 23 '18 at 9:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.