Find the directory part (minus the filename) of a full path in access 97

Question

For various reasons, I'm stuck in Access 97 and need to get only the path part of a full pathname.

For example, the name

c:\whatever dir\another dir\stuff.mdb

should become

c:\whatever dir\another dir\

This site has some suggestions on how to do it: http://www.ammara.com/access_image_faq/parse_path_filename.html

But they seem rather hideous. There must be a better way, right?

Answer 1

Is CurrentProject.Path available in Access 97?

Answer 2

That's about it. There is no magic built-in function...

Answer 3

I always used the FileSystemObject for this sort of thing. Here's a little wrapper function I used. Be sure to reference the Microsoft Scripting Runtime.

Function StripFilename(sPathFile As String) As String

'given a full path and file, strip the filename off the end and return the path

Dim filesystem As New FileSystemObject

StripFilename = filesystem.GetParentFolderName(sPathFile) & "\"

Exit Function

End Function

Answer 4

left(currentdb.Name,instr(1,currentdb.Name,dir(currentdb.Name))-1)

The Dir function will return only the file portion of the full path. Currentdb.Name is used here, but it could be any full path string.

Answer 5

If you're just needing the path of the MDB currently open in the Access UI, I'd suggest writing a function that parses CurrentDB.Name and then stores the result in a Static variable inside the function. Something like this:

Public Function CurrentPath() As String
  Dim strCurrentDBName As String
  Static strPath As String
  Dim i As Integer

  If Len(strPath) = 0 Then
     strCurrentDBName = CurrentDb.Name
     For i = Len(strCurrentDBName) To 1 Step -1
       If Mid(strCurrentDBName, i, 1) = "\" Then
          strPath = Left(strCurrentDBName, i)
          Exit For
       End If
    Next
  End If
  CurrentPath = strPath
End Function

This has the advantage that it only loops through the name one time.

Of course, it only works with the file that's open in the user interface.

Another way to write this would be to use the functions provided at the link inside the function above, thus:

Public Function CurrentPath() As String
  Static strPath As String

  If Len(strPath) = 0 Then
     strPath = FolderFromPath(CurrentDB.Name)
  End If
  CurrentPath = strPath
End Function

This makes retrieving the current path very efficient while utilizing code that can be used for finding the path for any filename/path.

--
David W. Fenton
David Fenton Associates