• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Simulating UNC paths when linking to tables

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
jrmcanada2
Asked:
jrmcanada2
  • 5
  • 3
  • 2
  • +3
5 Solutions
 
DultonCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
PatHartmanCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
jrmcanada2Author Commented:
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
 
DultonCommented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
Sorry for the multiple posts. The site stopped responding.
0
 
PatHartmanCommented:
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
 
Gustav BrockCIOCommented:
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
 
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
 
PatHartmanCommented:
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
 
jrmcanada2Author Commented:
Thanks to everyone who answered. I appreciate the variety of thoughts.

James
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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