[AccessD] Linked Tables - UNC Path

Cut and paste from a web page (needs some editing/rework)...

Option Explicit

Private Declare Function WNetGetConnection Lib _
   "mpr.dll" Alias "WNetGetConnectionA" (ByVal _
   lpszLocalName As String, ByVal _
   lpszRemoteName As String, cbRemoteName As _
   Long) As Long

Private Const NO_ERROR = 0
Private Const ERROR_BAD_DEVICE = 1200&
Private Const ERROR_NOT_CONNECTED = 2250&
Private Const ERROR_MORE_DATA = 234
Private Const ERROR_CONNECTION_UNAVAIL = 1201&
Private Const ERROR_NO_NETWORK = 1222&
Private Const ERROR_EXTENDED_ERROR = 1208&
Private Const ERROR_NO_NET_OR_BAD_PATH = 1203&

Private Sub Form_Click()
   Dim d As Integer
   Dim sRet As String
   Dim Msg As String

   For d = Asc("C") To Asc("Z")
      sRet = DriveLetterToUNC(Chr(d))
      If Len(sRet) Then
         Msg = Msg & Chr(d) & ": --> " _
            & sRet & vbCrLf
      End If
   Next d
   MsgBox Msg, , "Mapped Drives..."
End Sub

Private Function DriveLetterToUNC(ByVal _
   DriveLetter As String) As String
   Dim nRet As Long
   Dim Drv As String
   Dim Buffer As String
   Dim BufLen As Long
   Const MAX_PATH = 260

   If Len(DriveLetter) Then
      ' massage input string and create buffer
      Drv = UCase(Left(DriveLetter, 1)) & ":"
      Buffer = Space(MAX_PATH)
      BufLen = Len(Buffer)

      ' attempt to get UNC info
      nRet = WNetGetConnection(Drv, Buffer, _
         BufLen)
      If nRet = ERROR_MORE_DATA Then
         ' increase buffer and call again
         Buffer = Space(BufLen)
         nRet = WNetGetConnection(Drv, _
            Buffer, BufLen)
      End If

      If nRet = NO_ERROR Then
         ' return UNC name by trimming at 
         ' first null
         DriveLetterToUNC = Left(Buffer, _
            InStr(Buffer, vbNullChar) - 1)
      Else
         ' optionally output debugging info...
         Select Case nRet
            Case ERROR_BAD_DEVICE
               Debug.Print Drv; " --> ";
               Debug.Print "The specified " & _
                  "device name is invalid."
            Case ERROR_NOT_CONNECTED
               Debug.Print Drv; " --> ";
               Debug.Print "This network " & _ 
                  "connection does not exist."
            Case ERROR_CONNECTION_UNAVAIL
               Debug.Print Drv; ' --> ";
               Debug.Print "The device is " & _
                  "not currently connected " & _
                  "but it is a remembered " & _
                  "connection."
            Case ERROR_NO_NETWORK
               Debug.Print "The network is " & _
                  "not present or not started."
            Case ERROR_MORE_DATA
               Debug.Print "Buffer is too " & _
                  "small!"
            Case ERROR_EXTENDED_ERROR
               Debug.Print "An error has " & _
                  "occurred, call " & _
                  "WNetGetLastError."
            Case Else
               Debug.Print "Unknown error " & _
                  "code: "; nRet
         End Select
      End If
   End If
End Function


Scott Marcus
TSS Technologies, Inc.
marcus at tsstech.com
(513) 772-7000

 -----Original Message-----
From: 	accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]  On Behalf Of Mitsules, Mark S. (Newport News)
Sent:	Friday, May 07, 2004 10:45 AM
To:	'Access Developers discussion and problem solving'
Subject:	RE: [AccessD] Linked Tables - UNC Path

I was assuming that because the linked table manager visually shows the
links with drive letters and NOT UNC naming then the links were actually
hard coded to a particular drive letter.  Are you saying that, by default,
they are in fact utilizing UNC naming internally?  Because, while perusing
my modules I found one last instance where I'd neglected to use my constant
(which uses UNC).  Perhaps it was this last oversight that was giving me the
"path not found" problem all along?


Mark


-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Friday, May 07, 2004 10:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linked Tables - UNC Path


Hi Mark

> Lol.  I apologize...I think you're making the assumption that I have
> existing relinking code already in place...

Indeed, yes!

> I don't:(  This my first time at a TRULY multi-user app.  Maybe I
> need to be walked through this, but the  built-in linked table
> manager appears to use drive-letter mappings, not UNC.

Follow the advice of Steve.

> If I deploy the front-end to a person who doesn't have the same
drive-letter
> mappings, I get an error.
> What I was looking for was an approach that
> (1)mimics published relinking code, such as
> http://www.mvps.org/access/tables/tbl0009.htm,
> (2)forces UNC naming, yet
> (3)doesn't include possible user intervention.

That would be an "automatic" relinker but it wouldn't know where to
find your backend except if you supply the (UNC) path within the
application.
If you relink using UNC path before deployment, this step should not
be needed at all. 

/gustav


> Hmmm .. I'm not sure I can follow you here - if you have the UNC path
> it doesn't matter wether the user has a drive mapped to it or not. You
> just need to relink with the UNC path. But why relink if you have
> deployed the app linked to the UNC path of your choice?

> /gustav


>> Actually my situation is just the opposite.  I know the remote server
name,
>> but the user may have that drive mapped to any letter.  Therefore I would
>> like to be able to relink automatically using UNC.  I am not comfortable
>> with the user manually choosing the location of the BE.

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com