Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

sagamw

Technical User
Nov 11, 2009
104
GR
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?
 
I am sure it has something to do with the connect property, but I can't really return the string...
:(
 
To get the full file name, you can use:
Code:
Mid(currentdb.TableDefs("ALinkedTableName").Connect,11)
To get only the folder portion, you can find the length of the mdb name using
Code:
Len(Dir(Mid(currentdb.TableDefs("ALinkedTableName").Connect,11)))


Duane
Hook'D on Access
MS Access MVP
 
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.
 
By using the .connect function I get the following string

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

I use the mid function to cut the first part

Code:
MS Access;PWD=mypassword;DATABASE=

and i finally have the string

Code:
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

Code:
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"?



 
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

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top