How do I allow multiple people to open my Access database?

Question

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?

Answer 1

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

Answer 2

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