Dealing with File Names

Access applications can use Jet databases, other databases (as linked tables), or SQL Server. But sometimes you need to work with files on your hard disk. Bogdan Zamfir provides you with every function that you’ll need to work with file names–including converting from UNC names to mapped drives (and back again).

Many Access applications require file processing for “behind the scenes” tasks (backup and restore, importing and exporting data to and from various formats, automating other applications like Word or Excel, merging documents with data retrieved from a database). And file processing involves dealing with file names that may be local or remote files and can be accessed using mapped paths or UNC. In this article, I’ll give you all the functions that you need to make your life easier.

File name terminology

You’re probably confident that you know all about what a path name is. However, it’s worthwhile to review all the terms that are needed to discuss file names. To begin, since it’s possible to have several files with exactly the same file name, the only way to uniquely identify a file is to use the fully qualified file name. A fully qualified file name consists of two parts: the path name and the file name.

The file name is the actual name given to the file, when the file was created or renamed. The file name can be split into the stem (the file name without the extension) and the extension. The extension is considered to be the last letters after the last dot (.) found in a file name. Under the old DOS file system, the file name had to obey the 8.3 rule (eight letters for stem, a dot, and three letters for extension). Because the Windows operating system evolved from the DOS file system, the file name’s extension traditionally is three letters long. This is no longer required, so the extension can contain as many letters as needed, though the extension is still the portion of the file name that follows the last dot. If a file name has no dot in it, or the last character is a dot, then that file name has no extension.

The path name is the path from a root point of the file system to the file itself and is only logically connected to the file name. Unlike Unix-type operating systems, where there’s only a single root point in a file system and all local partitions and remote shared drives are connected as branches of this unique file system tree, the Windows operating system allows multiple independent roots in a file system. For local drives and partitions, a drive letter is assigned to each root. So a path name to a local file starts with a drive letter and ends with a file name.


Figure 1

The complete structure of a fully qualified file name located on a local drive can be seen in Figure 1.

For remote shared drives, there are two ways to refer to a file:

  • Map that shared drive to a free drive letter. The application can access a file located on that drive the same way it would access a local file, using a path name that contains the drive letter. Different computers may refer to the same remote shared drive by different drive letters.
  • Use the UNC path name. UNC, which stands for Universal Naming Convention, allows access to all shared resources in a network using a standard approach. Unless the remote shared drive has aliases assigned to it, all computers refer to the drive by the same name.

A UNC path consists of the server name, the share name, and the path name. A fully qualified UNC file name consists of a UNC path and file name (see Figure 2).


Figure 2

In this article, for clarity, I’ll use the term regular path name to refer both to path names from local drive and partitions, as well as to path names from mapped drives (that is, the path name in Figure 1), and UNC path name to refer to path names to shared files as shown (that is, the path name in Figure 2).

Functions for file name handling

The functions I’ve developed allow you to easily handle both regular path names and UNC path names. Some of these function names I’ve “borrowed” from Visual FoxPro, one of my favorite programming environments.

The first parameter most of those functions receive is a fully qualified file name (called cFullName in my sample code). The following rules apply to this parameter:

  • If cFullName doesn’t contain a backslash character (\), it’s considered to be a file name without a path. If cFullName doesn’t contain a dot character (or if the last character is a dot), the parameter is considered to be a file name without an extension. If the parameter contains a single dot character and it’s the first letter of the parameter, the file name is considered to contain only the extension.
  • If cFullName contains a backslash (\) as its last character, it’s considered a path name only (that is, without the file name). But if the last character isn’t a backslash, everything before the last backslash is considered to be the path and all letters after the last backslash are considered to be the file name.
  • If the first two characters are “\\”, it’s a UNC-style file name.

Some functions that work with path names return the path with a trailing backslash, and others don’t. To handle this uncertainty, the AddBS function adds a backslash to PathName only if the name doesn’t already have one. For example, both of these calls return the string “C:\Program Files\Office\”:

AddBS("C:\Program Files\Office")

AddBS("C:\Program Files\Office\")

Extracting components

The first set of functions allows me to retrieve portions of the file name. As you’ll see, I’ll work my way from the left-hand end of the path name toward the right, providing a function that returns each part of the name as shown in Figure 1 and Figure 2.

The JustPath function returns the path name from a full file name. It handles both UNC and regular full file names:

 If Left(cFullName, 2) = "\\" Then

    nPoz = InStrRev(Right(cFullName, _

      Len(cFullName) - 2), "\")

    If nPoz <> 0 Then

      JustPath = Left(cFullName, nPoz + 2)

    Else

      JustPath = cFullName

    End If

  Else

    JustPath = Left(cFullName, InStrRev(cFullName, "\"))

  End If

The JustDrive function returns the drive from a full file name, for a regular full path name. If the parameter is a UNC path name, the function returns an empty string. The function assumes that a regular full path name (either local or mapped) will have a colon (:) as its second character, following the drive letter:

If Mid(cFullName, 2, 1) = ":" Then

    JustDrive = Left(cFullName, 2)

  Else

    JustDrive = ""

  End If

Corresponding to the JustDrive function, the JustServer function returns the server name from a full file name for a UNC full path name. If the parameter is for a local path, the function returns an empty string. The function assumes that a UNC full path name will start with “\\”:

If Left(cFullName, 2) = "\\" Then

    nPoz = InStr(3, cFullName, "\")

    If nPoz > 0 Then

      JustServer = Left(cFullName, nPoz - 1)

    Else

      JustServer = cFullName

    End If

  Else

    JustServer = ""

  End If

Now that I’ve taken care of the server or drive letter, the JustFSRoot function returns the file system root of the path name. The file system root is the highest folder of the file system (that is, no folder can be created on a higher level on that file system).

The file system root is different depending on whether the file name is a regular path name or a UNC path name. On a regular path name, the file system root is simply the drive letter of the partition that file system was created on. For example, the file system root for path name “C:\Program Files\Office\MSAccess.exe” is C:. On a UNC path, things are a bit different. As I showed in Figure 2, a UNC path name starts with a server name. But on a server it’s possible to have several shared resources. For example, on drive C: of the server MyServer I could have two folders, named Accounting and Offers. I want to make them available on the network, so I share them as Accounting-drv and Offers-drv. Now if I browse the network from any other PC, I can see that the server MyServer has two shared resources, Accounting-drv and Offers-drv.

To make matters more interesting, the share can be mapped to a drive letter (for instance, Accounting-drv can be mapped as drive M:). This means that the folder M:\Accounting-2003 can also be accessed using the UNC path \\MyServer\Accounting-drv\Accounting-2003. From the UNC point of view, the file system root is \\MyServer\Accounting-drv; from the local file system point of view, the root is M:. Later in this article, I’ll show you a function that returns the UNC name for a mapped drive.

The JustFSRoot works by extracting the path, drive, and server components of the file name. Once those components are extracted, the code checks the results to determine what, exactly, is the root. For instance, if the path has no path, drive, or server, then the name has no root. If the name has a drive, that’s the root. If the path doesn’t have a drive but the file name as a whole is longer than just the server name, the function returns the server name as the root.

cPth = JustPath(cFullName)

  cDrv = JustDrive(cPth)

  cSrv = JustServer(cPth)

  If cDrv = "" And cSrv = "" Then

    JustFSRoot = ""

  Else

    If cDrv <> "" Then

      JustFSRoot = cDrv

    Else

      Dim nPoz As Integer

      If cSrv <> cPth Then

        nPoz = InStr(Len(cSrv) + 2, cPth, "\")

        If nPoz = 0 Then

          JustFSRoot = cPth

        Else

          JustFSRoot = Left(cPth, nPoz - 1)

        End If

      Else

        JustFSRoot = ""

      End If

    End If

  End If

End Function

The next step is to get the path name minus the server name, which is what the JustPathNoFSRoot function does (that is, either drive letter or \\server\share name).

cPth = JustPath(cFullName)

  cFSRoot = JustFSRoot(cPth)

  If cFSRoot = "" Then

    If Left(cPth, 2) <> "\\" Then

      JustPathNoFSRoot = cPth

    Else

      JustPathNoFSRoot = ""

    End If

  Else

    JustPathNoFSRoot = Right(cPth, _

         Len(cPth) - Len(cFSRoot))

  End If

End If

This routine is useful when you need to re-create an existing folder structure on another drive or share on a server. For each folder on the source drive, extract the path without the file system root and then combine it with the destination drive letter or \\server\share name to create the new destination path.

The JustFName function returns the file name only. It uses JustPath to extract the path name from the full name and return the remaining string:

  nPoz = Len(JustPath(cFullName))

  JustFName = Right(cFullName, Len(cFullName) - nPoz)

The JustStem function uses JustFName to extract the file name, and then return the stem:

If InStrRev(cFullName, ".") <> 0 Then

    JustStem = Left(cFullName, _

    InStrRev(cFullName, ".") - 1)

  Else

    JustStem = Trim(cName)

  End If

The JustExt function returns the extension from the file name:

 cName = JustFName(cFullName)

  If InStrRev(cName, ".") <> 0 Then

    JustExt = Right(cName, Len(cName) - _

      InStrRev(cName, "."))

  Else

    JustExt = ""

  End If

Combining functions

Once I had functions that can get any part of a file name, I used them to create a set of functions to build file names. The first set of functions replaces a part of a full file name with another string:

  • ForceExt–Creates a new file name by changing the extension. This is useful when you need to create a backup copy of a file, or to save an existing file to a different format.
  • ForcePath–Creates a new file name by changing the path. This is useful when you need to copy a file to a new location.
  • ForceStem–Creates a new file name by changing the stem. This is useful when you want to create a duplicate of an existing file (for example, MyFile.txt becomes NewMyFile.txt or MyFile1.txt).
  • ForceFname–Creates a new file name by changing the file name. This is useful when you want to create a duplicate of an existing file and replace the whole name (for example, MyFile.txt becomes OldMyFile.bkp or Nov12003.bkp).

The following example shows how I can use these functions to keep historical information about an MDB. This procedure is passed the name of an MDB file (in the variable cMDB) and creates a copy of the MDB in a subfolder called ARCHIVE. The new file has the same name as the original MDB file but with the processing date (in YYMMDD format) appended to it:

cNewStem = juststem(cMDB) & "_" & _

    format(date, "YYMMDD")

  cNewPath = juspath(cMDB) & "ARCHIVE"

  cNewDest = forcestem(forcepath(cMDB, cNewPath), _

    cNewStem)

  filecopy cMDB, cNewDest

The PathToUNC function converts a regular path name to a UNC path name. It uses the Win32 API function WNetGetConnection to retrieve the share name for a mapped path. I stripped out some code that wasn’t relevant to this discussion (the full source code can be found accompanying Download file):

Private Declare Function WNetGetConnection _

  Lib "mpr.dll" Alias "WNetGetConnectionA" ( _

    ByVal lpszLocalName As String, _

    ByVal lpszRemoteName As String, _

    nSize As Long _

  ) As Long

……

Function PathToUNC(ByVal cPath As String) As String

…

 If JustServer(cPath) <> "" Then

    PathToUNC = cPath

    Goto PathToUNC_Exit:

  End If

  cDriveLetter = JustDrive(cPath)

  If cDriveLetter = "" Then

    PathToUNC = ""

    Goto PathToUNC_Exit:

  End If

  nRemoteBufferSize = 1000

  cRemoteBuffer = Space(nRemoteBufferSize)

  nStatus = WNetGetConnection(cDriveLetter, _

    cRemoteBuffer, nRemoteBufferSize)

  If nStatus = 0 Then

    cRemoteBuffer = Trim(cRemoteBuffer)

    nPoz = InStr(cRemoteBuffer, Chr(0))

    If nPoz <> 0 Then

      cRemoteBuffer = Left(cRemoteBuffer, _

        nPoz - 1)

    End If

    PathToUNC = cRemoteBuffer & Right(cPath, _

      Len(cPath) - 2)

  Else

    PathToUNC = ""

  End If

PathToUNC_Exit:

End Function

Matching the PathToUNC function, the UNCToPath function accepts a UNC path and returns the appropriate mapped path (if one exists). It accomplishes this by retrieving the \\server\share part of the UNC path, and then tests to see whether that path is connected to any available mapped drive:

 …

  cLocalPath = ""

  cPath = Trim(cPath)

  If Left(cPath, 2) <> "\\" Then

    UNCToPath = ""

    Goto UNCToPath_exit

  End If

  cFSRoot = JustFSRoot(cPath)

  nRemoteBufferSize = 1000

  For i = Asc("A") To Asc("Z")

    cDriveLetter = Chr(i) + ":"

     cRemoteBuffer = Space(nRemoteBufferSize)

    nStatus = WNetGetConnection(cDriveLetter, _

        cRemoteBuffer, _

        nRemoteBufferSize)

    If nStatus = 0 Then

      cRemoteBuffer = Trim(cRemoteBuffer)

      nPoz = InStr(cRemoteBuffer, Chr(0))

      If nPoz <> 0 Then

        cRemoteBuffer = Left(cRemoteBuffer, _

          nPoz - 1)

      End If

      If cRemoteBuffer = cFSRoot Then

        cLocalPath = cDriveLetter & Right(cPath, _

          Len(cPath) - Len(cFSRoot))

        Exit For

      End If

    Else

      cRemoteBuffer = ""

    End If

  Next

  UNCToPath = cLocalPath

UNCToPath_Exit:

End function

The accompanying Download file contains only the Access 97 version of the MDB, but the module containing those functions (bzFileNameProc) can be easily imported into newer versions of Access databases. My functions can be used in any version of Access. However, they use two functions that were introduced in VBA with Access 2000: InStrRev and Replace. But I didn’t want to let this small inconvenience stop you from using them in Access 97, so I wrote my own version of InStrRev and Replace for Access 97 (they’re in the module called bzAcc2000Compat).

 

Your download file is called  Zamfir_tflproc.accdb
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.