Being an Access Database user you must have encountered issue while sharing up your database over the network. I know you must have tried every possible solution to resolve it soon but got nothing yet….!
Well such issues regarding sharing of the Access database for multi-user Access is severely encountered by many Access users, just have a look over one of the users complain.
I have an Access database that is currently configured to allow everyone opening it (and the parent folder) modify access, which I read was the required permission level in order to have multiple users with the file open at the same time.
However, the users keep coming up with an error that they “Cannot Open The File As It’s Already In Use”.
The database worked fine for years previously to now and no changes were made to their permissions structure or file configuration, so I’m a little bit confused as to what could be causing this or how to resolve. Everything I’ve read revolved around giving users modify permissions to the database file and the folder it resides in, which has already been done. If anyone has some insight that would be fantastic!
Thank you in advance!
Undoubtedly there are several ways to share an Access Database but this moreover depends on your needs and resource availability. This tutorial is about the options available to share Access Database and the benefits of each option.
3 Ways To Share Access Database On Network
Here are the methods that we will discuss one by one to share access database:
- Share Data By Using Network Folders
- Share a database by linking to SharePoint lists
- Saving a database to a document library
Method 1: Share Data By Using Network Folders
Share Data By Using Network Folders is the simplest option to share Access Database and has the least requirements. In this method database file is stored on a shared network drive and all users share the database file simultaneously.
Besides that it’s has some limitations too, like reliability and availability. Mainly when multiple user modifying the data; since all database objects are shared. This sharing technique also diminishes the performance as all the database objects are sent across the network.
This method is workful if few people are expected to use the database at the same time and users don’t need to customize the design of the database.
This method is less secure because each user has full copy of database file, which increase the chances of unauthorized access.
Steps To Share A Database Using Network Folder:
- First of all check whether the Access is set to open in shared mode on all of the user’s PC. It’s a default setting so you must check it out. If the database is opened in Exclusive mode, then it will surely interfere the data availability.
- Now start access application and under File option from the menu click to the option button.
- If you are an Access 2007 user then click to the Microsoft Office Button and then to the Access Options.
- From the Access options box, click Client Settings.
Note: if you are an access 2007 user then click to the Advanced option.
- From the Advanced section, under Default open mode, select shared, click OK and exit Access.
- Now copy the database file to the shared folder. After copying the file, make sure that file attributes are set to allow read/write Access to the database file. To use the database users must have read/write preference.
- On all users’ PC, just make a shortcut to the database file. In the shortcut properties dialog, put the path of the database file in the Target property by using a UNC address instead of a mapped drive letter.
For example, instead of F:\sample.accdb, use \\computername\shared.accdb.
Method 2: Share a database by linking to SharePoint lists:
Well Sharing a database by linking to SharePoint lists has the same benefits as that of split database and user can modify their own copy of database as the data is shared via the SharePoint site.
As the data is in SharePoint list, you can make it individually available over the network by using SharePoint features.
Here are the three main steps of this method:
- Shifting the data to SharePoint lists.
- Create links to those lists.
- Distribute the database file.
You need to use the Move to SharePoint Site Wizard to execute the first two steps and any means to accomplish the last steps:
Using the Export Tables to SharePoint Wizard
- From the Database Tools tab, in the Move Data group, tap to the SharePoint option.
- Well this option is available if your database is been saved in .accdb file format.
- For access 2007 users, go to the external data tab, in the SharePoint Lists group, and tap to the Move to SharePoint.
- Follow steps shown in Export tables to sharepoint wizard, specifying the location of your SharePoint site. To cancel the process tap to the Cancel.
- From the last page of wizard, make selection for the Show Details check box to see further details about migration.
This wizard page informs you about which tables have been linked to lists and provide information about a backup location and URL for your database. It will also throws warning if any migration issues encountered and gives the location of the log table where you can see more detail about the issues.
- Click Finish when the wizard completes its actions.
If the wizard displays a warning, review the log table and take necessary actions. For example, certain fields may not be moved or may be converted to another data type that is compatible with a SharePoint list.
In order to view your list on the SharePoint site, tap to the Lists on the Quick Launch, or hit View All Site Content. Just refresh the page in your web browser to make your lists appear on the quick launch on the SharePoint site. You can also make changes in the list settings on the SharePoint site.
Method 3: Saving A Database To A Document Library
The third method is to save the database to a document library. This method is quiet similar to saving a database a network folder and provide a convenient way to manage access to a database. As when you link to SharePoint lists, data is shared but not the database objects. Each individual user shares their own copy of the database.
- Open the Access Database which you need to share.
- From the File tab, click to the Save As option.
- Tap to the Save Database As, in the advanced section, select SharePoint and the tap to the Save As
For Access 2007 users: click Microsoft Office Button > Publish > Document management Server.
For Access 2010 database user : click File > Save & Publish > Save Database As > SharePoint.
- From the opened Save to SharePoint dialog box, search for the document library that you want to use.
- Check out the database file name and type and do if any necessary changes you need to do it.
- At that tap to the Save.
Now you must have got the clear idea on how to share your Access Database for Multi-user Access. So, try the mentioned solution carefully and do share your opinions with us in our comment section.