Solved

Simulating UNC paths when linking to tables

Posted on 2014-04-22
16
180 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
  • 5
  • 3
  • 2
  • +3
16 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 80 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 80 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 34

Assisted Solution

by:PatHartman
PatHartman earned 80 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
 
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 80 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 49

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 49

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 49

Expert Comment

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

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 49

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 80 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 34

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

15 Experts available now in Live!

Get 1:1 Help Now