MS Access - Multiple users working in one database

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.

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.


_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I saved it to a local folder, but it still gives me the following error if I want to split it...

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I found the error that kept me from splitting the database and have now split it. I copied the front end to a local (not shared) folder and send it from there to my colleagues (via email). The back end is saved in a shared folder. However, when my colleagues try to open it, they get the error:

Do you have any idea how I can fix it...?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I covered this in an earlier post:

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.

At present the link to the back end appears to be to a location on your local hard drive.  If you have not done so already, the back end should be moved to a location on your network, usually on a server, to which each user has full read/write permissions.  The links should then be refreshed to the new location.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You have to be VERY careful here.

The message means that your colleagues do not have the BE in that folder, which is on your machine.

If you are currently running the application and it is connected to that BE, and not to the copy on the server, you are entering data in the wrong BE.

I would recommend taking the version number out of the BE filename. It is not helpful in the long run.

Talk to whoever makes backups in your company and notify them of the folder where the BE resides, so they can include it in the backups. Of course the FE needs to be backed up too; it's not good enough to only have it on your machine.

You did save the FE as accde, didn't you? Highly recommended.

Best to use the Linked Table Manager and relink all tables to the server copy. Rename the BE on your local machine. Confirm everything works. Then send that FE to your colleagues.

There are better and more elaborate things you could do, but this is a good start for a beginner.

-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated November 19, 2020 Views 871 Applies to: