Updating Linked Tables to Back End Problem

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
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,678
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
51,177
Office Version
365
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,527
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:

Forum statistics

Threads
1,078,442
Messages
5,340,305
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top