VBA Ms Access - Get the Path to the Database (.mdb) File

It is often useful to obtain the path to the database (mdb) file, especially if you follow our tip to Store Images Using Relative Paths.

A common error is to assume that the 'current' path is set to the path of the database file (and therefore that images can be accessed using relative notation, for example). This can't be relied upon - some VBA functions can change the current path (eg "Dir"), and indeed the database can be opened with a different path altogether set as 'current'.

Below are 3 code-snippets you can use to obtain the path to the database (mdb) file in different situations. In each case if the database file is "C:\mydb\mydb.mdb" the functions return "C:\mydb\". These functions all work whether the database is opened via a local drive, mapped drive or a UNC path.

1) Access 2000 and later - Database Not Split.

If you only need to support Access 2000 and later, and do not have a split (front-end/back-end) database, then this is the simplest and most efficient method. If used in a split database architecture this returns the path to the front-end database - usually not what is desired.

Public Function GetDBPath() As String
    GetDBPath = CurrentProject.Path & "\"
End Function

2) Access 97 and later- Database Not Split.

If you need to support Access 97, and do not have a split (front-end/back-end) database, then use this method. If used in a split database architecture this returns the path to the front-end database - usually not what is desired.

Note that other approaches are possible which avoid the loop (and are therefore potentially slightly more efficient), but these either require use of the "Dir" function (which can give rise to recursion problems) or need additional references.

Public Function GetDBPath() As String
    Dim strFullPath As String
    Dim I As Integer

    strFullPath = CurrentDb().Name

    For I = Len(strFullPath) To 1 Step - 1
        If Mid(strFullPath, I, 1) = "\" Then
            GetDBPath = Left(strFullPath, I)
            Exit For
        End If
    Next
End Function

3) Split Front-End/Back-End - Get Path to Back-End.

If your database is a split (Front-End/Back-End) design, these functions return the path to the Back-End. The first version uses 'InStrRev'. InStrRev can give errors similar to reference problems on some systems (and is not available on Access 97), so a second version is provided which does not use InStrRev.

Using 'InStrRev':

Public Function GetDBPath() As String
    Dim strFullPath As String
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblLinked").Connect, 11)
    GetDBPath = Left(strFullPath, InStrRev(strFullPath, "\"))
End Function

Not using 'InStrRev':

Public Function GetDBPath() As String
    Dim strFullPath As String
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblLinked").Connect, 11)

    For I = Len(strFullPath) To 1 Step - 1
        If Mid(strFullPath, I, 1) = "\" Then
            GetDBPath = Left(strFullPath, I)
            Exit For
        End If
    Next
End Function