Link the frontend to 2 backends (1 Viewer)

Hi Guys

I have an Access database which is split down into frontend and backend. The backend is stored in L drive.

So what happened few weeks ago the L drive was down in my company and it took about 2 weeks to recover and we were not able to use that access database.

Although I daily make a copy of the frontend on my desktop so that it can be recovered when the database is corrupted. But I was not able to use that copy either bcos in that all the tables were linked to L drive.

Just wondering is there anyway to store the backend in 2 drives e.g S drive or L drive so when one is down then another copy can be used.

How the frontend can be liked to backends stored in 2 drives?

Any other suggestions on this guys?

Many Thanks

In short, the answer is yes; you can have as many backends as you desire. In fact larger applications will use this strategy to overcome the 2gb limitation.

However....

This is not what you are asking. If I understand you correctly you want to be able to link to the same data, in other words a backup copy, in the event the current one gets corrupted.

Let's address that one later. IF corruption is a recurring problem, then I would suggest devoting all your spare time in fixing that problem. Contingency plans are important, but even they will fail at the worst possible moment. The skill set of this forum is more than adequate to examine you setup and offer a solution to your corruption issues.

In the meantime, daily backups of your data is a prudent idea. Simply back them up and store them is a safe location in the event of the current one becoming unusable. When that happens, delete the current FE, BE or both and then redeploy the application.

But again, curing the cancer is much more important than treating the symptoms.

I feel like I channeled my inner Doc_Man with this response!

Well, Corruption is not a recurring issue. But when the L drive was down for everyone in the company and the backend was stored in L drive which didn't let us do anything for few weeks .

I'm just thinking just linking the frontend to 2 backends stored in different locations. Basically its the same backend ,same tables ,same data but stored in 2 different locations. How this can be done?

Any help will be much appreciated.
Thanks

You can't do this automatically with Access to the best of my knowledge. You are talking about data mirroring or replication. Access does not handle this when connected to an Access backend.

As already mentioned - you can take regular backups of your Backend Data saved to another location , and then restore that or have a system in your FE that would point to that if the first location isn't availabe, however you can't write the data to both at the same time, and have accurate replication.

Thanks Minty, in my company employees work between 8am and 8pm. I finish at 5pm. If I take a backup at 5pm and the users keep on using access database until 8pm and then the drive goes down , how can I recover the data after 5pm? hope it makes sense.

Or is there any way to create a backup automatically ?

Further to Minty's post, if you were to link simultaneously to two different virtual drives, Access would place a numeric suffix to the tables, forms, reports and queries to the second source. For example, tblEmplyees would become tblEmplyees1 and so on...making the second source unusable to rest of the application.

This is on a shared network I take it?

Minty said:

You can't do this automatically with Access to the best of my knowledge. You are talking about data mirroring or replication. Access does not handle this when connected to an Access backend.

Click to expand...

SO can this be done if we are using sql server as a backend and Access as a frontend?

NauticalGent said:

Further to Minty's post, if you were to link simultaneously to two different virtual drives, Access would place a numeric suffix to the tables, forms, reports and queries to the second source. For example, tblEmplyees would become tblEmplyees1 and so on...making the second source unusable to rest of the application.

This is on a shared network I take it?

Click to expand...

In case the first drive is down then I can use the backend saved in 2nd drive and change the table names from tblEmployee1 to tblEmployee etc....
and then link to the my coy of the frontend.

Will it work?

It would, yes. But I suspect these drives are virtual and not actual, physical hard disk drives. Do you know the answer?

Are there two questions here?

The problem of using a different drive when the server drive is down for a long period is to just change the location of the back-end through the Linked Table Manager and then distribute new front-ends to all users.

Logically this can all be automated.

Control of back-ups is an entirely different issue but should be simple, however instantly swapping automatically to a different location for a back-end is fraught with issues.

Tieval said:

The problem of using a different drive when the server drive is down for a long period is to just change the location of the back-end through the Linked Table Manager and then distribute new front-ends to all users.

Click to expand...

Not even that if you use drive mapping to get to the b-e. In my case, we have Y: mapped to the folder containing the back-end; when we lost the server drive last week (first time ever !), all we had to do was copy the last night's backup to a new drive and instruct all users to remap Y: to point to it.

BeeJayEff said:

...all we had to do was copy the last night's backup to a new drive and instruct all users to remap Y: to point to it.

Click to expand...

Which is why I use the UNC to map my BE instead of virtual drive letters. It ensures a connection no matter what drive letter is used.

LAN Administrators takes a daily,weekly,monthly back-up of Server Drives and keeps them safely. You can ask them to restore the database from the latest backup.

Linking to two databases in different locations is not a good idea, because you need to update tables of both copies every time to make them up-to-date.

Take a daily back-up of the BE database on your local drive, first thing in the morning when you open the FE. This will ensure that you have a previous day-end updated version of the database.

The following link explains how to take a daily backup of your BE database automatically, when you open the FE first time during the day:

Database Daily Back-up

aman said:

But I was not able to use that copy either bcos in that all the tables were linked to L drive.

Click to expand...

Why could you not use it?

It is a quick fix.

1) Re-map the L: drive to point to the new folder with the back end. Might have to do it for multiple users if not able to push it using a Log On script in an AD domain.

or

2) Relink the tables from L to the location with the copy of the back end. Redeploy the front end if needed.

This is one of the few times when UNC mapping actually works against you. Any other time, it is your friend. But in this case,...

Let's say that you had a file that, when listed via a command line DIRECTORY command or a Windows Explorer browse, it shows up as L:\OurDatabase\Project1\SharedBE.MDB (just to have a name for discussion.) L MIGHT internally map as \\Server1\SharedFiles\ or something like that.

Now Server1 bites the big one and is down for the count. The quick and dirty fix for this case is to create a shared-file path on Server2. Perhaps it could be \\Server2\OtherSharedPath\ (just to have another different name.) Under that folder, create folder pathing for \OurDatabase\Project1\ and put the copy of the database in that folder. Have all users manually remap their L drives to the new shared folder on Server2.

Now, let's ask the other question. Can you late-bind a back-end from a "naked" front end? Yes, but hoo DOGIES is it ever tedious. In essence you have to test from the front-end whether your preferred path to the back-end is available using code that either cannot trap or that traps cleanly on the "file not found" error that would tell you that you had a problem.

If the file exists where you expected you open from the preferred path. If not, you check your alternative path and, if the file is there, open THAT database. Why is this such a bear? A couple of quickie reasons come to mind.

1. False negatives could occur such that you attempt to open the alternate back end when the primary back end actually WAS available but the individual FE machine had a fault, not the primary server.

2. If case 1 occurs you can suddenly have divergent copies of the same database on two different servers and that means you AREN'T sharing what should have been a shared database. Reconciliation of what was done to the two disparate databases would not be trivial.

3. Further to case 2, if you weren't expecting to start sharing, there is no guarantee that the copy in the alternate location is synchronized with the primary copy. I.e. might be out of date, so would give you wrong answers anyway.

4. The coding required to do this is INCREDIBLY sensitive because you cannot make even ONE STATIC REFERENCE to the back end from the front end until all mapping has been tested, updated, completed, and verified. It is incredibly complex to get this right.

Oh... whatever you do - DON'T use automated "Compact & Repair" on exit from the front end if you are going to do this type of thing because you would store implied back-end table references if you did so. And in that case, the next time you opened the database when the primary was not available... BANG ZOOM.