DB in SharePoint, when multiple users edits, OneDrive can't merge changes. Even tho DB was split.

[sabukaru]

Probably an often ocurring (if not fundamental) problem in databases, but I am new to this.

I have a simple database with one table. As recommended by Microsoft, I split the db into one backend db, wich lives in a shared SharePoint folder, and multiple frontend db's, one for each user.

Everything works fine, but here's is the problem: Because it takes a few seconds for Access to update edits at all ends, if say User A adds a record and User B adds a record too, but does so before User A's edit gets reflected in User B's machine, OneDrive can't merge the changes and a secondary Access database gets created.

Before, I had my VBA application connected directly to the Access database in SharePoint (no split). I figured splittig the database would take care (or at least minimize) problems like this. But it seems that that is not the case.

Any suggestions into how to better handle this? Any help greatly appreciated. Thank you.

[Micron]

Just about everyone here will tell you that you are taking a great risk if you are trying to use an Access db over wifi or OneDrive (or similar). OD with Access and the transfer of data packets back and forth is a risky venture. IMO, OneDrive is for storing files that you download, work on, then upload as a changed file or as a backup drive. If this is happening because you need to work remotely, there are other options, such as Terminal Services (name might have changed) or Citrix. I'm sure others who are more knowledgeable about this will chime in.

[sabukaru]

Thanks Micron. Yeah... for this reason I have built a few workarounds in the VBA. For example, pinging to google.com before making changes to the db, etc.

Since this is only for a few user (3-4), will only be updated a few times a month, and the vulnerability is open only for a few seconds, I'm going to risk it and see what happens. If it is too much to handle then I'll see what else I can do.

Thank you for your help and any other suggestions always welcome!

[Micron]

Make sure you do regular backups - at least daily, I would say. Depends on how much data loss you want to risk.