UNC Path to Backend Not Working on Network; Need a Mapped Drive Alternative

Our network is such that UNC paths don't work.  I know that they should work. But they really don't, and I have no control over that.  (I think it has something to do with our network being a cobbled hybrid of Novell and Microsoft, as they stopped working about that the time that the cobbling began.)

This leaves me with trying to connect to the back end via a mapped drive.  Only not all my users have the same mapped drive, and again what drive they have my backend's location mapped to is out of my control.

Is there any way to cycle through the mapped drives via code till the right one is found and then connect?  Right now I prompt them to browse to the backend, but I'd like to make it automatic.  Just can't figure out how to check for mapped drives.
Who is Participating?
1WilliamConnect With a Mentor Commented:
Your users may complain of the additional time it takes to get to the application due to the desire to re-link each time. A config table will work for you though.  you have a login form.  The user name is tied to a row on the config table,  A colum would contain the path to the BE.  Run  some code to do the re-link and then back to business as usual.
Here is some of the code I use:
Dim tdf As TableDef

Sub ChangeLinks(DPL$)
Dim rs As Recordset
Dim dbn$, c$, p$, j%, tmp$
Dim Changed As Boolean
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select * from DB_Paths")
    DoCmd.Hourglass True
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) Then
            If tdf.Connect & "" <> "" Then  ' it's a linked table
                If tdf.Name = "MSysAccounts" Then
                    SysCmd acSysCmdSetStatus, tdf.Name

                    c$ = tdf.Connect
                    For j = Len(c$) To 1 Step -1
                        If Mid$(c$, j, 1) = "\" Then
                            dbn$ = Mid$(c$, j + 1)
                            Exit For
                        End If
                    rs.FindFirst "DB_Name = '" & dbn$ & "'"
                    If rs.NoMatch Then
                        MsgBox "Error! " & dbn$ & " not found!"
                        tmp = tmp & vbNewLine & dbn$ & " not found!"
                        GoTo NextTable
                    End If
                    Select Case DPL$
                        Case "D"
                            p$ = rs!Dev_Path
                        Case "P"
                            p$ = rs!Prod_Path
                        Case "L"
                            p$ = rs!Local_Path
                    End Select
                    If Right$(p$, 1) <> "\" Then
                        p$ = p$ & "\"
                    End If
                    If p$ <> "" Then    ' if no database to link to, skip it
                        tdf.Connect = ";DATABASE=" & p$ & dbn$
                        On Error Resume Next
                    End If
                    If Err <> 0 Then
                        MsgBox "Table: " & tdf.Name & vbNewLine & "Error: " & Err.Number & vbNewLine & Err.Description
                        tmp = tmp & vbNewLine & "Table: " & tdf.Name & ",  Error: " & Err.Number & ",  " & Err.Description
                    End If
                    Changed = True
                End If
            End If
        End If
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
    If Changed Then
        PrintLinks False, tmp$
        MsgBox "No links to change."
    End If

    Set rs = Nothing
End Sub

Sub PrintLinks(Optional PrintNonLinkedTables As Boolean = False, Optional tmp$)
Dim o$, j%

    o$ = GetPathToDB & "\" & App_Name & "_Links.txt"
    Open o$ For Output As #1
    Print #1, db.Name
    Print #1, ""
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) Then
            If tdf.Connect & "" <> "" Then  ' it's a linked table
                Print #1, tdf.Name, tdf.Connect
                If PrintNonLinkedTables Then
                    Print #1, tdf.Name; " ***** NOT LINKED *****"
                End If
            End If
        End If
    If tmp$ & "" <> "" Then
        Print #1, ""
        Print #1, ""
        Print #1, "ERRORS:"
        Print #1, tmp$
    End If
    NoteIt o$
End Sub

Sub NoteIt(fil$)

    Shell "notepad " & fil$, vbMaximizedFocus
End Sub

You can do away with the opening of the text file at the end, it is more of a developer check.
You probably ought to provide each user with a copy of the FE, mappead as need
What if u created a local table in each FE
this can act as the user's config table
here the user can enter their mapped drive and u can have a button on that form which relinks all tables

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

long story short - no. given what you have described there are too many variables to account for. 1William & rockiroads  have offered viable solutions, but they still require configuring and you asked about automation. It sounds like you work at a small company and they have a tendency to grow. If your company is growing, your network need will only increase. I would start lobying hard for someone to un-f%%k your cobbled network and set one up the right way.
Sorry for the bad news
adriadneAuthor Commented:
There are too many users to make individual copies of the FE feasible.

The config table might work except it's really not any less work for the users than having them browse to the linked tables each time there is a new version of the front end.  (Right now they only have to relink the tables when there is a new version, but they still gripe.)  
adriadneAuthor Commented:
Fanopoe wrote:
It sounds like you work at a small company and they have a tendency to grow. If your company is growing, your network need will only increase. I would start lobying hard for someone to un-f%%k your cobbled network and set one up the right way.

LOL.  Actually it's got about 100,000 plus employees, just a really ummm...interesting...IT infrastructure.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Typically you would re-link the tables on startup. 1William has given you code, and I would urge you to review the code here for relinking procedures: http://www.mvps.org/access/tables/tbl0009.htm. The acutal path, once determined, could be stored in the HKeyCurrentUser registry hive, which is unique for each user (assuming they login from their own workstation, or you have roaming profiles perhaps that would work as well?? not sure about that). If the setting is stored in the HKCU registry, then it really doesn't matter how often the frontend is changed ... the fronted would always look in the registry for that particular user, and relink based on the value there. Of course, the users would have to manually relink once (and your code would store it in the registry) ... the only other time they would need to relink would be if the backend path changes ...

<There are too many users to make individual copies of the FE feasible.>

I have several apps with hundreds of users (non-concurrent, of course) who use individual frontends. I built my own utility to deploy frontends, but there are many utilities out there which make the distribution process very simple; some are more "user friendly" than others.

Tony Toews has a free one here: http://www.granite.ab.ca/access/autofe.htm

One is the Access Frontend Loader (http://www.frontend-loader.com/) which runs as a service and "detects" when a new frontend has been deployed. This is, to me, one of the best methods I've seen.

There are plenty of others out there also, but since your mapped drives aren't consistent, you may be better off with the last link.
One more thing you can do is 'read' the users network login like this:
Type wksta_user_info_1
    wkuil_username As Long
    wkuil_logon_domain As Long
    wkuil_logon_server As Long
    wkuil_oth_domains As Long
End Type
Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName$, lpnlength&)

Public Function GetNTUserID() As String
Dim ret As Long
Dim buffer(512) As Byte
Dim cbusername As Long
Dim username As String

    username = ""
    username = Space(256)
    cbusername = Len(username)
    ret = WNetGetUser(ByVal 0&, username, cbusername)
    If ret = 0 Then
        GetNTUserID = Left(username, InStr(username, Chr(0)) - 1)
        GetNTUserID = ""
    End If

End Function
Private Sub ShowMe()
    MsgBox GetNTUserID
End Sub

Declare Function WNetGetUser& Lib "mpr.dll" Alias "WNetGetUserA" (ByVal lpName$, ByVal lpUserName$, BufLen&)

Public Function GetNovellUserID()
    Dim lpName As String, NovellUserLoginID As String
    NovellUserLoginID = Space$(55)
    WNetGetUser& lpName$, NovellUserLoginID, Len(NovellUserLoginID)
    GetNovellUserID = Left$(NovellUserLoginID, InStr(NovellUserLoginID, Chr$(0)) - 1)
End Function
Private Sub ShowMe()

    MsgBox GetNovellUserID()
    If IsCompiled = False Then  ' This checks to see if, due to modifications, the app needs to be compiled.
        MsgBox "The application needs to be compiled!", vbCritical, "Application Requires Compilation"
    End If
End Sub
' Note:  Running the NT 'GetNTUserID' returns the same value on a Novell network (NT servers there too, but the login is Novell).
' Is there a reason not to always use that one instead?
' I did not test running this (GetNovellUserID) on an NT network to see what would happen

Then, using that, you could query your config table, get the approriate path, run the re-linking

with a config table, users only have to select their mapped drive once

Then when new tables are out, u can send an email to the users or implement some kind of message of the day feature

A backend table which has a message in it

On your startup form, u can check this table, if any messages, then display in a message box

sMsg = NZ(DLOOKUP("msg","tblMOTD"),"")
if sMsg <> "" then msgbox sMsg

Users can then go to a form, click a button which relinks all linked tables for them
I wont give the code as its already supplied

The relink code though will build the path using the mapped drive in their config file
The path is always the same, just the mapped drive will be different

adriadneAuthor Commented:
Thank you.  I'm going to try a variation on what you suggested with the path copied to both a local configuration table (in case relinking is necessary without a new front end) and to a mirrored configuration table on the backend that I will copy to the front end before each new release.  Hopefully that will at least reduce the need to browse to the back end each time a new version of the front end is released.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.