Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Simulating UNC paths when linking to tables

Posted on 2014-04-22
16
Medium Priority
?
310 Views
Last Modified: 2016-05-29
I have an Access 2010 database that is divided into a front-end .accde and a back-end .accdb. All the tables are in the back-end and the front-end links to them.

My client is wondering if we can use UNC paths to point to the back-end database so that he doesn't have to map a network drive on the end-user PC's.

My problem is that I'm not sure how to handle this with regards to my development system. Right now, we're using mapped drives so I have the same drive letter mapped on my development system on my network. Then when I put a new front-end on the client's server, the links work fine. But if we switch to UNC, I'll have the problem that the computer name of the server on the client's network isn't the computer name of the server on our development network.

What's the best way to handle this? Do I need to add code to relink the tables (if necessary) on boot?

James
0
Comment
Question by:jrmcanada2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
16 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 320 total points
ID: 40015833
you could do it as necessary, but that's a lot of overhead for an infrequently called job

I'd store both path's in a table
MyUncTable with fields "Env" and "Path"

store "DEVL" as Env and your development unc path as Path
and "Prod" as Env and your development unc path as Path.

-Then I'd write an administrative type procedure that would switch the linked tables over to the other UNC on demand.... that way you don't burden the application with checking on every startup. I've done it and found at times can make access a little fragile.

If you don't want to expose the paths in a table, you could bury them hard-coded in the module, but I wouldn't do that.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 320 total points
ID: 40015851
"My client is wondering if we can use UNC paths to point to the back-end database so that he doesn't have to map a network drive on the end-user PC's."

I highly recommend that you ONLY use the Full UNC Path when linking to back ends when deployed. I never, ever use mapped drives - which of course can be changed or deleted.

On  your development, just link to a local folder containing the back end.
And if nothing else (like auto relinking code), you can use the Linked Table Manager to relink (on site) to the UNC Path .

I basically do this at work ... daily ...

mx
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 320 total points
ID: 40016044
All of my databases include relinkers.  I don't always make this feature available to the users since they shouldn't be moving the BE around but I would go crazy switching between test and production if I couldn't automate it a little bit.  Most of my apps have multiple BE databases and the Linked tables manager is hopeless when there are multiple BE's involved so in self defense I built my own.  

Here's a link to some useful tools including a relinker that you can add into your app.

https://www.jstreettech.com/downloads.aspx
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 75
ID: 40016059
Yes I meant to mention Armen's Relinker tool also.  And he demo'd a new beta version of this tool at the Access Day seminar last week in Irvine CA.
Highly recommended.

mx
0
 

Author Comment

by:jrmcanada2
ID: 40016351
Hi Dulton,

Your suggestion seems pretty workable. But I'm running into a bit of a problem.

I do my development in the development environment on my PC. Then I create the .accde which I copy to the client's server.

At what point do I use the admin function to change the linking? If I do it on my PC, then it fails because it can't see the UNC on the client's server. Do I have to find a way to invoke the link changing function after I've copied the .accde to the client's server?

James
0
 
LVL 6

Expert Comment

by:Dulton
ID: 40016405
Yes, you've hit the nail on the head. You can't run the job until you're in the destination environment.  Which, if you're doing it in an .accde, you'll probabaly have to expose something to call the routine.
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 320 total points
ID: 40016953
Ask the client to map that drive. It's really a non-issue and - as you have seen - will remove a lot of trouble and various work-arounds.

If the users "happen" to delete mapped drives, supply a logon script or command file.
For a logon.cmd command file, it's one line of code:

net use g: \\servername\foldername password /user:username /persistent:yes

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40016959
Ask the client to map that drive. It's really a non-issue and - as you have seen - will remove a lot of trouble and various work-arounds.

If the users "happen" to delete mapped drives, supply a logon script or command file.
For a logon.cmd command file, it's one line of code:

net use g: \\servername\foldername password /user:username /persistent:yes

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40016960
Ask the client to map that drive. It's really a non-issue and - as you have seen - will remove a lot of trouble and various work-arounds.

If the users "happen" to delete mapped drives, supply a logon script or command file.
For a logon.cmd command file, it's one line of code:

net use g: \\servername\foldername password /user:username /persistent:yes

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40016962
Sorry for the multiple posts. The site stopped responding.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40017459
Gus,
Mapping the drive is not possible in a lot of companies.  They may already have many drives mapped and all your users may not have the same drive letter available.  If they can't all use the same drive letter, then each user needs to be able to do his own relinking which we usually want to avoid.  The safest option is to distribute the FE already linked to the shared BE.

James,
You don't actually need to see the drive to map it.  Just type/paste in the unc path.  You do have to have access to the drive to complete the linking though so you can't do it from your own environment.  The FE does need to be on the client's network when the link happens.
Pat
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40017538
They may already have many drives mapped and all your users may not have the same drive letter available.  If they can't all use the same drive letter, then each user needs to be able to do his own relinking ..
I have heard this before but in my 28 years in business (since 10-Net, PC-LAN, 3Com 3+Share, and Novell NetWare 2.0) I've never met a client where this little piece of order in chaos couldn't be established at little efforts. After all, most managers look for efficiency, and having the users freed from bothering with drive mappings is one step.

/gustav
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 320 total points
ID: 40018587
Joe and Pat, thanks for the mention of our J Street Access Relinker.

James, what we do is this:  When we give the new FE to the client, we put the "master" copy on their server.  We run it from there just once, in order to have our Relinker prompt for the new location, which we specify using UNC.  Then we distribute the master (now properly linked) FE to all the users' PCs using whatever method is desired.

Hope this helps,
Armen Stein
J Street Technology
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40018827
I've never met a client where this little piece of order in chaos couldn't be established at little efforts
Welcome to my world.  Large companies are more likely to have a problem since they have more servers and more defined shares.  Even then you don't always run into a problem.  Usually it happens when someone on the team has some cross-over function that causes him to have to interact with several departments.
0
 

Author Comment

by:jrmcanada2
ID: 40020227
Thanks to everyone who answered. I appreciate the variety of thoughts.

James
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

722 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