Get the Path to the Database (.mdb) File

Sample Newsgroup Questions

Determining Database Folder Location
Application.CurrentProject.Path in Access 97 (How?)
Obtain Path of Access Project
How to know the access database file directory/path name?
Path to db application
app.path in access 97 module
Does access have an application path?
Get the path of the current access db
Finding the Path to the Database in VBA
Get Database Path Accessed

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

Related Articles

Get the Path to the Database (.mdb) File
Parse the Folder or Filename from a Full Path
Use Relative Paths for Linked Images

Imaging for Access that's Easy, Efficient & Fast

NO OLE Bloat

NO App Dependencies

NO Complex Coding

NO Performance Penalty

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.