Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Link access 2010 backend tables in a different directory

Posted on 2014-10-01
12
Medium Priority
?
335 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 58
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 40

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 58
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 40

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

783 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