Updating Linked Tables to Back End Problem

[gheyman]

I have a Back End (BE) with all my Tables. When I am solely in the Front End (FE) Database everything runs fine. As soon as another user opens and uses the Front End database the database somehow is loosing the links. We get a message that it doesn't recognize the back end and we have to refresh the Links. Once the Links are refreshed it works fine until its closed. Once we reopen the FE the same problem happens. What's really confuses me is that if I am the only user getting in and out of the front end all day, there are no problems. I never have to refresh the table links.

Any ideas what's happening?

 

[gheyman]

I will check to see if we are Mapped the Same. But would that screw up my Links if someone else was mapped differently? I assume the answer is yes, but want to confirm

 

[Joe4]

We all use the same FE. No one has their own copy of the FE

As I said, that pretty much negates most of the benefits of using a split database, and can cause issues exactly like you are experiencing now, as well as worse problems (corruption).

I strongly, STRONGLY encourage you to give everyone their own copy of the front-end (I cannot stress that enough!).
Is there some specific reason why you aren't doing that?

To avoid the issues with linked tables where users have different drive mappings, use UNC file paths instead of mapped paths.

 

[gheyman]

Joe; If we all had our own FE doesn't that mean that every time something is changed on a FE (ie new form, report....) we have to go to each individual and copy over their FE? We will have almost 50 users. I though the purpose was so you can do modifications/updates while still having the

 

[Joe4]

You have experienced one of the issues with sharing the same front-end (when people's computer have different network drive mappings).
You can also have issues if they are trying to use the front-end simultaneously.
Even worse, if not everyone is using the same version of Access, you can end up corrupting the front-end (we had that happen a lot while we were slowly transitioning the company to a new version of Access).

The way that I used to handle it.
1. Put a copy of the front-end in some network location everyone has access to
2. Put a batch file on their desktop that does the following:
- a. Clear out the copy of the front-end on their computer
- b. Copies over the front-end from their network location to a specified drive on their hard-drive
- c. Opens the database from the location it was just copied to

They are opening the database by clicking on the batch file that copies down a fresh copy of the database each time, and opens that.
So all you need to do to update the front-end is to replace the copy on the network (since they are actually running a fresh copy each time).
Problem solved! No one has to do anything special to make sure people are running the most current version.

I give details on how I did this (along with the code) here: https://www.accessforums.net/showthread.php?t=49744&highlight=executable
You could actually add another statement to your code to make sure the directory your will be copying the database to already exists, and if it does not, create it first.

 

[xenou]

I think there are two basic reasons for splitting the DB (there are probably more than two!)

• To protect the DATA from corruption (i.e., if forms or reports cause a problem the data tables are in a different file - I think this is because in general forms and reports are much more complicated than tables and are the mostly likely parts of the database to have problems with corruption. This happened to me only once but it was an easy fix to replace my FE and not have to worry about my BE at all). I consider this the most important really.
• To spare you as much as possible from the other problems such as the one you are now having - drive mapping issues and whatever else might come with user specific settings and multi-user problems.

Generally, if you have a lot of users the practice is to provide an update mechanism so that when users open the database if there is a new "version" of the FE they will (ideally) have a new version installed (or just copied over to their PC, basically).

Really if you have 50 users or more you should consider a more robust database server for your backend.


EDIT: Note, what he said above since I type to slow!

 

[tambadal]

Joe4,
Thank you for you reply, I am no familiar with UNC file. How can I change mapped paths to UNC? Will it fix the problem that I am having, seems like the issue is we might have different mapped path?Thank you!

 

[Joe4]

See here: https://stackoverflow.com/questions/15473263/update-an-access-linked-table-to-use-a-unc-path