MS Access - Multiple users working in one database

Question

Hi All, I have been working on a database in access. It is almost done and I have done some trials with my colleagues. However, whenever two or more colleagues are working in the document simultaneously, it creates a separate document with the computer ID after the document's name with the changes of that colleague.

I would like to automatically merge all the changes/edits from all colleagues in the same document... I have tried to split the document, but then it only creates a backend, not a front end.

Underneath I have attached a screenshot of the advanced settings that I currently have for the document.

Thanks in advance for your help.

Answer 1

Firstly, Access files are not 'documents'.  Access is a development environment.  Each file contains a number of objects which are saved individually, e.g. when a row entered into a table via a bound form is committed to the table, rather than the file being saved end bloc as a 'document'.  

To use Access in a multi-user environment you must split it into separate front and back ends, no ifs , no buts.  Having multiple users open a single unsplit file is an open invitation to corruption.  Even a single user operational database should be split, as this protects the data in the case of any object in the front end becoming corrupted.  It also makes backing up the data regularly very easy.

I don't know where you went wrong in trying to split the file.  I'm assuming you used the built in wizard.  This normally works without problem, but it is a simple task to split a file manually:

1.  First make sure no other users are using the file.

2.  Create a new empty .accdb file as the back end, and import all tables from your current file into it by means of the New Data Source icon in the External Data ribbon.  Select From Database, then Access from the drop down list.  In the ensuing dialogue select the import tables etc option, and browse to your current file. Click OK and in the next dialogue select all items from the Tables tab.  In the same tab select Options, and then ensure that the Relationships check box is selected.  Click OK to execute the import.

3.  Once you are sure that the new back end has been correctly filled with the tables from your current file, close the back end file and open the current file.  In this select and delete all tables, so that you are left only with queries, forms etc.  This file will be the front end.

4.  In the front end, do as in 2 above, but this time browse to the new back end and select the Link to the data source etc option rather than the Import option.  You'll notice that this time there is no option to import the relationships.  Relationships are only relevant in a back end.  While it is possible to create relationships on a front end they do absolutely nothing of any use, so it's pointless to do so.

5.  Once the links have been created in the front end it should work in exactly the same way as your current unsplit file.

6.  You now need to install the new back end in a shared location on the system to which all users have full read/write permissions.

7.  A separate copy of the front end should be installed on each user's local machine, or in a 'personal' location on the system, to which only they and the appropriate administrator(s) have access.

8.  In the event of the back end being relocated it will be necessary to refresh the links in each user's front end.  This can be done with the built in Linked Table Manger, or you can use a custom solution, examples of which you'll find in Refresh.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In the zip archive the RefreshLinksSingle_07.accdb file illustrates how a front end can be set up to automatically check for invalid links at start-up by means of code in the front end's opening (unbound) form's Open event procedure.  Alternatively you can simply copy the frmUpdateLinks form from my file into each front end, and open the form manually whenever you need to refresh the links.

Answer 2

> it creates a separate document with the computer ID after the document's name with the changes of that colleague.

That seems to indicate you have the Access file in SharePoint or OneDrive or similar location. That is a mistake. Access does not work that way. Rather the back-end must be in a shared folder on the network - typically a file server, and the front-end on each workstation, e.g. in c:\users\public\YourAppName.

Answer 3

Hi Ken, thanks a lot. I have tried to follow your steps and set up a front and backend. However, it gave me the following error: 

I don't have it open anywhere and tried to restart my laptop a few times in the meantime...

Do you know by any chance a way that I can fix this? 

Thanks again. 

Best regards, 

Annika

Answer 4

Hi Tom, true it is shared on the Sharepoint. Once I solved my error message when trying to set up a front and backend, I will save the back end in the shared folder and ask my colleagues to download the front end to their personal folders.

Answer 5

You've confirmed in your reply to Tom that the file is curently in SharePoint.  If you copy it to a local folder on your PC you should have no trouble splitting it there.  The back end should then be moved to a shared location on your server, to which all relevant users have full permissions, and the links refreshed  Then copy the front end to each user.