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).
