Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Different logical locations for backend database

    Hello again, Colleagues All !
    Here is the scenario for a little problem I am wrestling with.

    The client has a LAN where the backend database sits on a server and each user has a PC where the server appears as drive F:. I do development work remotely and run a copy of the system where the backend is loaded on C:. Two of the managers connect remotely and on their laptops, the server appears as drive Z:. What I need is a VBA editor for the linked tables databases which will automatically amend the drive component of the link depending upon the machine.

    I propose to have a table on the front end with a one-field record holding the drive name as text. Then, on startup, the autoexec() subroutine would read the name, modify (or refresh as the case may be) the links and then hand over control to the user. I have some code for refreshing known links, but cannot find anything to actually change thelink specifications.

    The front ends distributed to the users are all .mde files, but at present I have to give the managers a .mdb file and then allow them general access to relink the tables themselves. This is, of course, a fairly substantial security issue (not to mention the possibility that, not being techies, they could compromise the system by making mistakes).

    Can anybody advise me, please.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If possible, the tables should be linked via UNC, so you don't need to worry about the drive mapping. Instead of:

    F:\FileName.mdb

    you'd link with

    \\ServerName\ShareName\FileName.mdb
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I usually use the UNC name but I think your issue might be more involved than that.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Nice one Paul. Beat me by a few secs.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Great minds think alike!
    Paul

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Thanks for the advice guys. I will consult the sysadmin to find out exactly how the shares are organised. Logging in remotely via rdp, I take over an office PC, so I don't really see this stuff. One question, though. Let's say that I am setting up a front-end. I click on New, navigate to the location of the backend and select the tables to link. How are the server and share details made visible in Access ? I am sure I did a lot of similar stuff years ago (c. 1990, I think !) but my more recent experience was with Informix and such-like on Unix boxes, so bear with me if I seem to be a bit like a newbie.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    When you're setting up the front end, instead of navigating through the mapped drive to the back end when linking tables, navigate through Network Places. You should be able to look in Windows Explorer at the mapped drive to see where it is. On my PC, I see things like

    ShareName on ServerName (P

    That gives you the server and share name to navigate to. For existing front ends, you can see the existing path in the Linked Table Manager. You can check the table(s) and the box that says "Always prompt for new location" to relink them through Network Places.
    Paul

  8. #8
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I did pick up a couple of comments on another forum which may be of interest. Firstly, while use of the UNC is an elegant and versatile solution, it can adversely affect performance. Certainly, when I tried it on my clients system, it was very slow, but I am not sure whether it was the technique, the fact I was using an rdp connection, or my semi-rural wireless internet connection. However, someone else came up with another neat suggestion. Store all of the link pathnames in a table on the front-end and build or rebuild them on startup, using the DoCmd.TransferDatabase method. I like the idea of this because the front-end is delivered without any links (so while the programming IP could be copied, the data remains safe). Also the the table entries can be user-specific so that the program can only be run on the user's machines (e.g. laptop or PC or even a Palm Pilot type of box). I will try this and report any problems.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •