Get Database Location

To get database location, we use getDbsLocation function to specify Access database location that will be opened. It is using strResult variable that can be a string value or expressions. The function result is a string file path name. The complete getDbsLocation function is as follows:

Function getDbsLocation() As String
  Dim strResult as String
On Error GoTo Err_Msg
  strResult = "D:\Access\Database_be.accdb"
  getDbsLocation = strResult 
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getDbsLocation, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

Implementation:

If the strResult variable is  string value, then write the file path name, as can be seen in the above function. The function above use "D:\Access\Database_be.accdb" string value as the database location.

If the srtResult variable is an expression, then set the strResult value using expression. Example:

strResult = DLookup("[Database file path name]","[Table name]","[pathDefaultStatus]=True")

where [Database file path name] is the field that its value is taken from table [Table name] and it is set as database default, indicated by field [pathDefaultStatus]=True.

Table name

pathfFile	pathDescription	pathDefaultStatus
D:\Access\Database_be.accdb	Local database	True
\\Access\Database_be.accdb	Server Database	False

Using the table [Table name] above, we can control strResult to select D:\Access\Database_be.accdb as the database location. Therefore, the strResult expression is as follows:

strResult = DLookup("[pathfFile]","[Table name]","[pathDefaultStatus]=True")

or

strResult = "D:\Access\Database_be.accdb"