Jan 11 2022 05:38 PM
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?
Jan 11 2022 09:14 PM
Jan 12 2022 07:40 AM
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.
Jan 12 2022 11:01 AM
Jan 12 2022 11:06 AM
Jan 12 2022 01:45 PM
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.
Jan 12 2022 01:46 PM
Jan 12 2022 01:54 PM
If you want to try SharePoint lists, here's a video of a presentation Albert Kallal did for my AUG chapter meeting.
Jan 13 2022 01:34 AM
Jan 13 2022 02:20 AM
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
Jan 13 2022 06:34 AM
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.
Jan 13 2022 01:59 PM
Jan 13 2022 02:01 PM
Jan 13 2022 02:45 PM
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:
Jan 13 2022 03:16 PM
Jan 13 2022 04:42 PM
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.
Jan 13 2022 05:58 PM
Jan 13 2022 07:30 PM