Constant database conflicts

Copper Contributor

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?  

23 Replies
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.

@JasonC888 

 

One should never use DropBox, OneDrive, or similar locations for the back end accdb of an Access rel.... 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.

 

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

thanks for the information.  As we are a small group (5) I think we can do a SharePoint solution.  I will give that a go.  Thanks again.

 

@JasonC888 

thanks. And FYI prior to covid the front end was linked to the network copy and it worked fine.
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?

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

@JasonC777 

 

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.
Thanks David. I am going to give remote desktop a try as we already have desktops for each user in the office. Thanks

@JasonC777 

 

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:

 

Access Roadmap 

 

Microsoft Access Dataverse Connector announcement 

Good to know thanks
I am not impressed by what I've seen of Dataverse so far. For very low end applications, perhaps it's acceptable. One of the benefits is that it abstracts away a lot of the details. One of the drawbacks is that abstracting away details leaves you with a limited ability to customize or expand.

At this point, I don't know any serious Access developer participating in the public beta, and none have expressed interest in doing so.

We also have no idea yet about performance; whether it will be any better than with SharePoint lists or not. Time will tell.

@George Hepworth 

 

I am not a professional developer so I am looking at this from a different viewpoint. It may turn out to be exactly the feature I need even though it would not be given a second look by a professional developer. I have been hopeful and cautiously optimistic since this was added to the roadmap.

 

But as you say, time will tell.

For a lot of reasons I hope it does pan out. Microsoft deferred nearly all other work on Access for a year while they worked on the Dataverse connector. Perhaps a sense of momentum lost on other features of wider appeal is part of the lack of enthusiasm. A . Still, it does prove that MS remains committed to Access so we can take some comfort from that.

"Still, it does prove that MS remains committed to Access so we can take some comfort from that."

Fully agree with you. That is one reason I hope the Dataverse connector is a great success for Access users and Microsoft.