[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Link access 2010 backend tables in a different directory

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
ssmith94015
Asked:
ssmith94015
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
ssmith94015Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
PatHartmanCommented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
ssmith94015Author Commented:
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
 
PatHartmanCommented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now