Solved

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

Posted on 2006-07-10
10
508 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:adriadne
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 18

Expert Comment

by:1William
ID: 17072776
You probably ought to provide each user with a copy of the FE, mappead as need
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17072794
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

0
 
LVL 11

Expert Comment

by:fanopoe
ID: 17072876
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
0
 

Author Comment

by:adriadne
ID: 17072903
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.)  
0
 

Author Comment

by:adriadne
ID: 17072998
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.
0
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.

 
LVL 18

Accepted Solution

by:
1William earned 500 total points
ID: 17073058
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
        Else
            If tdf.Connect & "" <> "" Then  ' it's a linked table
                If tdf.Name = "MSysAccounts" Then
                Else
                    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
                    Next
                    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
                        tdf.RefreshLink
                    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
NextTable:
    Next
             
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
             
    If Changed Then
        PrintLinks False, tmp$
    Else
        MsgBox "No links to change."
    End If

    rs.Close
    Set rs = Nothing
   
End Sub

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

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

Sub NoteIt(fil$)

    Close
    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.
0
 
LVL 84
ID: 17073274
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.
0
 
LVL 18

Expert Comment

by:1William
ID: 17073513
One more thing you can do is 'read' the users network login like this:
NT
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)
    Else
        GetNTUserID = ""
    End If

End Function
Private Sub ShowMe()
    MsgBox GetNTUserID
End Sub

Novell
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17073569
adriadne

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
e.g.

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

0
 

Author Comment

by:adriadne
ID: 17076491
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.  

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 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

13 Experts available now in Live!

Get 1:1 Help Now