Updating Linked Tables to Back End Problem

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,835
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,835
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,490
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,835
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,490
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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!
 
Last edited:

tambadal

New Member
Joined
Oct 31, 2019
Messages
10
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!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,126,904
Messages
5,621,571
Members
415,847
Latest member
nigeywigey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top