Forum Discussion
Constant database conflicts
We operate a small law office. We have used Access for our client database for many years and it worked well for us until Covid lockdowns started. The primary data file is saved as .accdb file and each user uses an individual Access form linked to the data file. Our primary data file as saved to dropbox and saved locally on a file server. The user interface links to the dropbox folder on the file server. This all worked fine for years. When lockdowns happened and people started working from home they now have a local copy of the dropbox folder on their laptops. They still have a local interface file which links to the primary database file in dropbox but also saved to their laptops and back to the file server in the office. We now constantly get conflicted copies of the database and lots of entries go missing. Does anyone know of a way to deal with this please?
One should never use DropBox, OneDrive, or similar locations for the back end accdb of an Access relational database application. It's surprising, in fact, that it ever really worked for your organization. My guess is that users were not trying to work with it simultaneously before. Or perhaps they were actually using the version in the network folder, not in Dropbox.
That's the only safe way to do it.
If you must allow remote access, and that's a lot more common today, then you'll need to find a suitable method of sharing the data in the back end.
One option is migrating data to an environment which CAN support multiple users, such as SharePoint lists or a SQL Server database, hosted or remote.
Another option is to user remote desktops to have users connect to an inhouse copy of their Accdb FE on a computer inside your network.- thanks. And FYI prior to covid the front end was linked to the network copy and it worked fine.
- i maybe wrong, but you are working on a Local (shared) dropbox then.
now you are connecting it via internet.
as with many other comments out there, dropbox, onedrive, google drive, etc is not a best
place to host ms access backend. - I concur with my colleagues. You should never use Dropbox, Google Drive, OneDrive, or any of those file sharing services for Access databases. I get asked this so much, I put a whole page together about it, with some optional alternatives: https://599cd.com/AccessOnline
- This is a very bad idea.
Clouds services are not intended for multiuserjobs, ask youself how access works: it open the accdb file, read and write and eventually close the file, at that moment dropbox (or onedrive or whatever you have) upload your file on the cloud and other users download the accdb file.
What do you think happen when two or more user modify their local copy of the accdb file at the same time?
You need a vpn for all users (not vpn for privacy, your own vpn installed at your office), so they can connect directly to the accdb file in your server/pc at the office.
Ciao If you want to try SharePoint lists, here's a video of a presentation Albert Kallal did for my AUG chapter meeting.
- Thanks George. I will look at that tonight
- Hi brainstrust. (I'm not sure why I had to register again but somehow I now have a new account). I thought that SharePoint was going to be our solution but our database has around 17,000 records in the primary table with 2 child tables, one with around 30,000 records and the other with around 9,000 records. It will continue to grow. From what I have seen on the youtube linked below that is going to be unworkable with SharePoint. (pity as it seems a good process). What cloud based systems will work for a database of that size?
Well, ONE option certainly is to go to a VPN, but I'd hardly say you "have" to take that path. Many organizations do that very successfully, of course. I would NOT try to run such a setup with the FE on your home computer and the BE in the office. In my experience, performance is marginal. When I had to do that, it was okay for me, as a consultant, to log in for a brief maintenance/trouble-shooting session, but I wouldn't ask a user to do that all day every day for their regular work.
Remote Desktop works great. In that scenario, there needs to be a dedicated computer inside the office for each user, though. In other words, available hardware is going to be a factor. That said, it's much better performing where it can be done. You could also set up Remote Desktop server but I have no experience with that and can't speak to how well it would fit your scenario.
There is at least one other option to consider, remote hosted SQL Server or other server-based database (MySQL, etc.) Performance is a consideration, as is cost, but it eliminates the need for having in office computers dedicated to external users.
- Thanks George, that might be the solution. We already have desktops in the office for everyone. We would just have to remember to turn them on. I'll do some more research and give it a try. Thanks again for your help.
Hi JasonC777 ,
you have to setup a vpn at office. This will allow you to connect to your back end at the office from your front end at home.
of course it work as well as how fast is your connection at home and at office (look at both connection speed, download and upload to chose)
The second solution is a remote connection (anydesk, teamviewer or similar) to connect to your pc at the office from your pc at home. This solution il better for performance of the database (because data don't traver over the internet) but you have to have PCs always on at the office for every user.
Well, actually anydesk (and I think other) allow to swich on your pc remotely, so you can suthdown your pc and start it remotely when needed.
Ciao
- Thanks David. I am going to give remote desktop a try as we already have desktops for each user in the office. Thanks
This is not a today solution but Microsoft is developing an Access Dataverse Connector which is in preview and scheduled for release in March, 2022. Although there will be some limitations, the general idea is that you will be able to move your local tables to the cloud database but otherwise use Access on the desktop just as you do now. This will also enable you to set up better security for your data.
Of course, there are other solutions such as moving your backend data to Azure SQL. However, if you are a “Citizen Developer” like I am, you may be more comfortable letting Microsoft do the heavy lifting on the back end.
For more information:
- Good to know thanks
Thanks for the tips everyone. I think I have found a very easy and free solution. Each staff member uses the same google account on laptop for home and desktop in the office so I have simply set up Google Chrome Remote Desktop and they can each access their desktops at work which we can turn on each day they are working from home. Simple. Cheap (Free) and so far it seems to work quite effectively. JasonC888
- Google Remote Vs MS RDP - each have their pros and cons, The Google RD uses the resolution of the host PC. RDP uses the full display resources available on the Remote device. I've yet to find a way to send F11 (or Atl+F11) via Google remote to open the nav pane or VBE but for most users this isn't an issue. Google RD is easier to setup but MS RDP is not too hard, you just need a Win Pro OS. Where the Google Remote shines is its assist mode where two people can both see and effect the same PC at the same time. That's useful if you need to remotely assist one of your staff. You can't do that with MS RDP.
- There are still some people in the office so we will just turn on the desktops of those working at home during the relevant days and set them so they don't go to sleep during the day.
