Using a local Backup copy of my Server BE (1 Viewer)

[Kenshep]

Hi All,
I have developed an Access Database for the Local Hospital Volunteers and Fundraisers, it currently holds about 1000 records and probably won’t get a lot bigger. The Database holds personal details, with photo and also records present and past hospital and fundraising experiences. I have been working with the Volunteer Manager over the past two years to get it to where it is now stable in design, the only changes are the names of Fundraising events. The Database has quite a few Reports such as birthdays, work statistics, training and safety and contact details, it also allows her to Email all, groups, sub groups or individuals depending on what training or Fundraising is happening. The Database is split with the Front End (FE) on two office PC’s and the Back End (BE) located on the office Server (G: Drive).

The Server is backed up nightly to another drive and weekly to an off-site drive. I have also created a Backup icon on the Managers PC which will back up the BE to the Managers C: Drive, and the manager uses that backup a couple of times a week or whenever there are significant data changes.
The Manager would like to have access to the local copy on C: Drive in a view only mode when the server is down because in the past few months it has been down quite a bit and she feels like her hands are tied without access to the data.

I have developed a function that is called whenever the server is down that will advise the user on PC2 that the Server is not available and to see the Volunteer Manager. The Volunteer managers PC1 will also advise that the server is down but ask if the Manager wants to use the local copy of the Database in a view-only mode. If the Manager says no it just goes back to the Desktop, however If the Manager says Yes then the BE will be linked to the FE in the same directory on C: Drive and the Manager is advised after a successful linking to use the ‘Server Down Database’ icon on the Desktop and after clicking ‘OK’ it returns to the desktop to allow her to use the Server down icon.

My Question is, using VBA, is there a nice way to switch over to the local backup copy of the BE, can I make the switch almost seamless, do I import the data, or should I have the FE and the BE in the same directory on the C: Drive, link them and get the user to click the Server down icon on the desktop as I plan to do now. I haven’t actually linked the FE to the BE as yet, just looking for the nicest way to achieve it, is it a good idea? will it work?

Cheers, Thanks in advance. kenshep

 

[isladogs]

Hello and welcome to AWF

First of all, you could automate the backup process e.g. as a scheduled task to run each night

Secondly, yes you can switch backend files fairly easily. In fact I believe there are several backend switch utilities on this forum including one by Pat Hartman IIRC

The basic idea is to have 2 tables with info about linked tables
a) list of table names
b) list of locations (in your case with 2 records)
To switch, the routine loops through & breaks the existing connections, then loops through the table list again & reconnects to the new selected location
The process should only take a few seconds

BUT it is important to ensure the local copy is NEVER updated or there will be instances of lost data unless it is then made live ... which itself is very likely to cause issues.
You said the local copy will be READ ONLY. If so, that's fine.

 

[Pat Hartman]

Is the server really down enough to warrant this switching? Although switching BE's is very simple if you use a form and the users can be trusted to do this, I still worry about it. I would go so far as to lock down everything except reports when working with any BE except the current one.

 

[Kenshep]

Hi Guys, thank you for your prompt replies, Ridders I will setup a scheduled task for daily backups good clue. I am still having trouble finding the Backend Switch utility but it is definitely much simpler than I thought.


Pat, I understand your concern over the data, but I only give the option to look at the data and take reports to the Volunteer Managers Terminal, any other Terminal just says 'Server Down see the Volunteer Manager". The Database produces an Emergency Contact Report with dialable telephone numbers which is uploaded to the cloud and links sent to all the organisers so they can access it on their iPads at the event. Last year the server went down the day they were to produce the contact list and they had to resort to a list three days old and make a couple of amendments, it was for a big event with about forty volunteers.

Thank you both, I will try to locate the code and see how I go, thanks again.


Kenshep