Getting the Old path of a backend before the re-linking of FE & BE

[sagamw]

I have a db split in FE and a BE.

I already have implemented a piece of code for relinking the 2 ends (with a select folder dialog) in case of the BE has moved.

How can I get (through VBA) the OLD PATH of my back end?

I mean if the BE was in "c:\dbold" and I move it to another location, how can I get the String "c:\dbold" (BEFORE of course) I choose another dir and link it to the new location?

 

[sagamw]

I am sure it has something to do with the connect property, but I can't really return the string...

 

[sagamw]

I found some infos here

http://msdn.microsoft.com/en-us/library/bb221162(v=office.12).aspx

but feel free to comment on my original question

 

[dhookom]

To get the full file name, you can use:

Mid(currentdb.TableDefs("ALinkedTableName").Connect,11)

To get only the folder portion, you can find the length of the mdb name using

Len(Dir(Mid(currentdb.TableDefs("ALinkedTableName").Connect,11)))

Duane
Hook'D on Access
MS Access MVP

 

[MikeC14081972]

Just a small tip when using paths and linking tables

Always use the full UNC path as this will allow for users who have network drives mapped differently.

 

[sagamw]

By using the .connect function I get the following string

MS Access;PWD=mypassword;DATABASE=c:\mydir\mydb.accdb

I use the mid function to cut the first part

MS Access;PWD=mypassword;DATABASE=

and i finally have the string

c:\mydir\mydb.accdb

My routing checks at first if there is mydb.accdb in c:\mydir\ and if not opens a "folder browser" to select the new dir. It's something like

If FileExists(theremainingstring)= false then
.....pop-up the folder browser

It works fine, but I am a little confused about the "full UNC path".
Haven't tried over a network yet (with BE in another PC".
I am seeing the "network" as a location in my folder browser over my hard drives.
Will I get into troubles, if I use my routine (that cuts the 1st part of the .connect string and search for the BE with the FileExists function) because of this "UNC path"?

 

[MikeC14081972]

Just a little tip.

Say you run this over a network and your mapping is

N:\MyBackends\backend.mdb

another user may have

M:\MyBackends\backend.mdb

The front end would connect on yours but not on the other users. By using the UNC path \\server\share\MayBackends\backend.mdb it doesn't matter how other users have their drives mapped it will still connect.

I use the below function to retrieve the UNC path

Option Compare Database

Option Explicit

   ' 32-bit Function version.
   ' Enter this declaration on a single line.
   Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias "WNetGetConnectionA" _
                (ByVal lpszLocalName As String, ByVal lpszRemoteName As String, lSize As Long) As Long

   ' 32-bit declarations:
   Dim lpszRemoteName As String, cbRemoteName As Long
   Dim lSize As Long, lStatus&

   ' Use for the return value of WNetGetConnection() API.
   Const NO_ERROR As Long = 0

   ' The size used for the string buffer. Adjust this if you
   ' need a larger buffer.
   Const lBUFFER_SIZE As Long = 255

Public UNCPath As String


Function GetUNCPath(sPath As String) As String
Dim DriveLetter As String, sFolder As String

DriveLetter = Left(sPath, 2)
sFolder = Mid(sPath, InStr(sPath, ":") + 1)
      ' Specifies the size in characters of the buffer.
      cbRemoteName = lBUFFER_SIZE

      ' Prepare a string variable by padding spaces.
      lpszRemoteName = lpszRemoteName & Space(lBUFFER_SIZE)

      ' Return the UNC path (\\Server\Share).
      lStatus& = WNetGetConnection32(DriveLetter, lpszRemoteName, _
         cbRemoteName)
      ' Verify that the WNetGetConnection() succeeded. WNetGetConnection()
      ' returns 0 (NO_ERROR) if it successfully retrieves the UNC path.
      If lStatus& = NO_ERROR Then

          GetUNCPath = Trim(Replace(lpszRemoteName, Chr(0), "")) & sFolder
            UNCPath = GetUNCPath
      Else
         ' Unable to obtain the UNC path.
         'MsgBox "Unable to obtain the UNC path.", vbInformation
         GetUNCPath = sPath
      End If

End Function

 

[jrbarnett]

MikeC14081972,

While a UNC pathname will indeed always work regardless of drive mappings, it is a fair bit slower to access data than a mapped drive an mapping via that.
This can become a problem if you have a slow network connection or a large database (or both).

John