Forum Discussion
Form replacing previous data entry, not saving previous one
"I have created an Access database, shared with a work colleague on Onedrive and Sharepoint. "
This is risky, at best.
Access is designed to support multiple users making simultaneous changes to one or more records in one or more tables in a single accdb. Perhaps more accurately, it is designed to support the ability for multiple users to change one or more records while other users are adding or modifying other records.
The file format required to make this happen is supported on Windows -- but not in SharePoint.
A single user can create or modify records in an accdb stored in SharePoint, but when two or more users try to do that, one always wins. I.e. The last person to make any change in an accdb stored in SharePoint is the person whose changes are saved. Other changes by other simultaneous users are lost. It's even possible to create corruption this way.
Move the accdb to a local network, in a shared folder to which all users have permissions.
Also, if you have not yet split the accdb into a Front End, with only the interface and code objects, and a Back End, with only the tables, do that now. Put only the Back End on the network. Put a copy of the Front End on the computer of each user. Link all of those copies of the Front End to the shared Back End. Never let two or more people share a single Front End.
The specific problem you describe is not one that has been reported, but it is quite reasonable to think that it is an artifact of trying to run the Access accdb from SharePoint.
ThanksGeorge_Hepworth . Yes, discovered, shortly after my post, that using Sharepoint wasn't a good idea. I've never used Sharepoint but am more familiar with Dropbox. Only started using Sharepoint and other 365 programmes due to a requirement to have to switch to using MS for securty protocol reasons. Now operating the database access via Onedrive.
You suggest splitting into a front end and back end. As a new user to Access, I wouldn't know how to do that wihtout googling some tutorials. Is it easy / straight forward? Can you signpost any guidance / links? The other users all work remotely, if that makes a difference. They are trusted users but newbies to Access like me.
The problem I have described also hapens when the other person using the database accesses it via Onedrive and when the file is downloaded. I had thought it could be an issue with compatability between versions of Access but a google search doesn't suggest that is an issue. Any other thoughts gratefully received!
Thanks again.
Unfortunately, DropBox is no better than OneDrive in this regard. Neither OneDrive nor DropBox is designed to support the kind of simultaneous partial updating required for Access accdbs to work. Both follow the rule "Last Saved Change Wins All the Marbles".
In other words, if you make a change to any record in an accdb which is stored in OneDrive or DropBox, then the ENTIRE accdb is replaced when you save that one change. That means everything anyone else did with their copy before your change was saved is overwritten, not just the parts you changed.
When you make a change to any record in an accdb stored on a drive on a Windows network, ONLY that one record is changed when you save it, leaving other parts of the accdb unchanged.
You need to explore other options for storing the accdb containing the data.
SharePoint LISTS could work, although they come with their own limitations. Other options could be a remotely hosted SQL Server database, or SQL Azure. Or perhaps even DataVerse tables.
However, the first step is to split the accdb into a Front End, with only interface objects like forms, reports, queries and code, and a Back End, with only the tables. Then you can decide where to store the data.
- Thanks for this. I'm looking into splitting the database now into a front and back end. Whether or not that corrects the problem the other user is having in Forms I don't know but short of any other answers on this Forum or Googling, it's worth a try! Thanksa for your suggestions.
- As long as the data is in an accdb stored on OneDrive, DropBox, or similar location, the problem WILL persist.
