Auto-Copy and Re-use Back End

Question

Hi all

I have a split access database which I am looking to make available to remote users.

Having no web development knowledge I wanted as robust an access only solution as possible.

So, editing users will be remoting to network PCs and running the front end from these machines connecting to what I will call the live back end.

For non-editing remote users I was looking to copy the live backend to another location on a nightly basis and have their front ends link to that.  If the remote connection caused corruption of the copy back end I could just re-copy from the live back end, or wait for it to be overwritten by the nightly routine.

The questions I have are:

Is it "safe" to copy the live back end while people are accessing it?

Similarly, if I overwrite the secondary backend while a lock exists on that file, would it implode?

Thanks

Phil

Answer 1

Copying a live Access database is not recommended, but people do it all the time.

Replacing/overwriting an actively used Access database that has a lock file will not be possible, Windows won't allow it.

Why not migrate the backend to Azure and relink your front-end.  Then distribute the Fe to each user?  Then everyone will have access to the data and you won't have to worry about any of this.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Thanks Daniel

My understanding was that access does not work well with the front and back ends split over any kind of WAN.

Or at least that there was a much greater chance of corruption due to latency.

Is this old thinking?

Phil

Answer 3

Typically this is very true, but working with Azure is a different thing.  

That said, like with using any other rdms with Access, you need to optimize your application to obtain the best performance possible.  Load forms with no records, only retrieve single (or minimal) records based on user selections, ...

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

Hi Phil

Daniel has given you some excellent advice already. 

As regards the backup, I would recommend you only copy the BE when it is not in use. To manage this, setup an auto kickout routine at a specified quiet time of night to ensure all users are out of the database first then lock it whilst performing the backup.

This can easily be automated using e.g. Task Scheduler

You may also find this parallel thread worth following: 

https://social.msdn.microsoft.com/Forums/vstudio/en-US/99933f53-3662-4a6e-9bca-8fd6657245f4/best-practice-to-roll-out-updated-fes?forum=accessdev