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. The functionality in Forms works for me. I enter data for a new record (some of the basic data is drawn from a linked table autmoatically) and any new data I enter saves as typed in.
When my colleague tries to do the same thing, when he enters data for a new record and moves onto the next, the new data for the new entry then automatically changes the previous entry's data. In effect, if he carries on entering data in for new records they will all be the same as the last one entered. Any thoughts on what the problem is for him? A Google search did not reveal any obvious answers and I assume it is possibly something to do with his settings as the Form works fine for me.
Thanks in advance.
- George_HepworthSilver Contributor
"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.
- PlanSTACopper Contributor
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.
- George_HepworthSilver Contributor
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.