Thursday, February 16, 2017

Access 101: How Do I Find The Path and Filename For My Database?

Access 2000 and Later

In Access 2000 and later, this is easy. Access provides an object called CurrentProject, which refers to the currently opened database. This object as two properties (among others) called Path and Name.

So to return the path and name, you can do this:

'declare a couple of variables
Dim strPath as String
Dim strFile as String
Dim strPathName as String

'call the properties
strPath = CurrentProject.Path
strName = CurrentProject.Name
strPathName = strPath & "\" & strName

Of course, if you only want the whole path and file name, you don't actually need the path and name variables. You can do this:

strPathName = currentproject.Path & "\" & currentproject.Name

Access 97 and Earlier

Access 97 doesn't have the CurrentProject object, but it does has the CurrentDb object. This can be used in a similar fashion.

CurrentDb also has a property called "Name", but unlike CurrentProject.Name, CurrentDb.Name returns the entire path and file name.

'create string variables
Dim strPathfile as String
Dim strPath as String
Dim strFile as String

Next we set the database variable to the current database. Access provides an object called CurrentDb to do this:

strPathfile = CurrentDb.Name

Since this returns both the path and the file name, you need to separate them:

strPath = (Mid(strPathfile, 1, Len(strPathfile) - Len(Dir(strPathfile))))
strFile = Dir(strPathfile)

No comments: