Solved

Simulating UNC paths when linking to tables

Posted on 2014-04-22
16
204 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 35

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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 35

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 35

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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