Solved

Link access 2010 backend tables in a different directory

Posted on 2014-10-01
12
323 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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.

929 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

12 Experts available now in Live!

Get 1:1 Help Now