Solved

Link access 2010 backend tables in a different directory

Posted on 2014-10-01
12
324 Views
Last Modified: 2014-10-03
I am trying to develop code that will link to the backend tables in a subdirectory for an Access 2010 database.  Problem is the various users have different mapping for their systems.  However, they can all get to the directory, but on one machine the directory path is mapped with an X and on another it may be with a F.  So, I have a local table in the front end database that only holds their directory path mapping, which they must enter either by typing or copy paste.  Users are scatter around California.  This is a low-usage database and they are not concerned about system slowness.    I want a procedure that when they click "Link Tables", the code will use the path to locate the back end and link the tables.  All the code I have found on the web requires the front and back ends to be in the same directory, but that is not possible in this case.  Am looking for suggestions.   I will do more searching, but hope for some help.  

Sandra
0
Comment
Question by:ssmith94015
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 40355931
Sandra,

I can post some linking code tomorrow.   What it does is check a link, and if it fails, allows the user via a file dialog box to locate the BE.   Once they choose the file, a refresh of the links is done.  That handles users with different map pings.

Jim
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40355966
Armen Stein has a nice relink utility here:

http://www.jstreettech.com/downloads.aspx

It's the J Street Access Relinker, and it's freely available and redistributable.
0
 

Author Comment

by:ssmith94015
ID: 40356130
Jim, thank you as I am simply worn out and done for the day so samples tomorrow would be welcome.  Scott, will also check the link tomorrow, I am too tired to think right now.  Fortunately, the deadline was moved from Friday to Monday and I need the rest!
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57
ID: 40356712
OK, here's the linking code.  I've been using this for quite a number of years (I wrote this back in the A2 days).   It's old and rough, but quite serviceable.  Not sure if you'll want to use this or Armen's, but you'll probably want to use Armen's as I'm sure it's more polished then what's here.

Holler if you have any questions.

Jim.
Relink2.Zip
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40357273
The best solution is for you to use the UNC name when you link the tables originally.

\\newfiscal\Afox\AFox\AccessApplications\Database\AOAClientManagement_DataVer140925.accdb
rather than
F:\AccessApplications\Database\AOAClientManagement_DataVer140925.accdb

That way, it doesn't matter how each user has his drives mapped and he doesn't even need to have a drive mapped to the BE location.  So, if you link to the production BE using the UNC and then distribute the FE to the users, it will work for EVERYONE on your network.  No individual relinking is required.
0
 

Author Comment

by:ssmith94015
ID: 40357508
Jim, am trying your code and thank you.  Pat, that was the first information l asked for but no one could (or would) provide.  I don't think the "tech" person knew what or understood what I was trying to get so this is a work-around as I did not want to push it.

Sandra
0
 

Author Closing Comment

by:ssmith94015
ID: 40358291
Thank you both.  I like each and wound up using ideas from each.  This was the last  coding to develop and test and I am relieved.  

Sandra
0
 
LVL 57
ID: 40359266
<<This was the last  coding to develop and test and I am relieved.  >>

That's good to hear.

<< I like each and wound up using ideas from each. >>

 Hum, maybe you should put together a sample database of what you did, then next time a linking question comes up, you'll have your own example to use?

 Or you could do it as an article and then point to that.

 Food for thought....

Jim.
0
 

Author Comment

by:ssmith94015
ID: 40359357
Actually, I did that.  I have template database with code repeatedly used and this went right into it.   Frankly, over the years, a LOT of your answers went in there!
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40359748
If the UNC (no coding required) method is what you were after, I don't understand why you chose to implement a solution that requires the users to relink the BE themselves.  There are certainly uses for the relink solutions.  In fact, I include them in all my applications but they are primarily for my use so I can easily switch between test and production BE's.  I do expose the relink to the user in the apps I sell where I am not directly involved in installation but I always restrict it to admins only.
0
 

Author Comment

by:ssmith94015
ID: 40359796
Hi Pat

I do not work at this company, it was a contract position.  Problem is that they did indicate the database will be moved in the future compounded by the fact that users are all mapped differently.  In the timeframe I had to work with (one week) to get everything done, this was the fastest solution that I thought of.  Any suggestion for an alternative are appreciated, but this was simply an expedient methodology for this problem as there were more complex issues to resolve.

Sandra
0
 

Author Comment

by:ssmith94015
ID: 40359826
There will be a training video for the users on how to do this and no, this was not an ideal solution.  Each user at least will have a local front end so that was not a problem, but ensuring they can relink to the backend via their differing directory mapping was critical.  I utilize relinking as you do but additionally there will be no administrator with database knowledge.  I had to train the manager on basic housekeeping (backup and compact repair-another video for them).  The biggie for this project was making absolutely sure all the very complex math and data extraction was solid.  Fortunately, it is and ultimately and thankfully, the client is very happy.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question