Solved

Link access 2010 backend tables in a different directory

Posted on 2014-10-01
12
320 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
 
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 34

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now