This might be a little confusing so please bear with me. I have excel sheets that contains data I want to use to update an Access Database. The sheets containing the data do not contain the macro I want to run. I don't want them to be macro enabled sheets. So I created a simple excel sheet that is macro enabled and contains the macro that reads in the data and updates the DB. I have the user open the excel sheet containing the macro then open the sheet containing the data. The macro from the first sheet then shows up on the data sheet when the macro tab is clicked. I want to make the reference to the access database relative so if the excel sheets and the data base are put on a different computer and different directory it would still work. Note: The excel sheet with the macro and the database WILL be in the same directory, but the excel may not be in the same directory.

Below is the code I have tried but I keep getting a Compile Error: Constant Expression Required where the word .Path is highlighted. The other concern I have is that if the excel sheet with the data IS in a different directory that it will not get the path from the macro excel sheet but from the path of the data excel sheet. I have also upload all of the code I am using in case someone need to to see the whole thing. I hope someone can help me.

Public Function UpdateLoans() As Boolean

Dim MBM_Loansdb As DAO.Database
Dim MBM_Record As DAO.Recordset
Dim myPath As String
myPath = Application.ActiveWorkbook.Path


Const DB_Location = myPath & "\MBM_Loans.accdb"
If MBM_Loansdb Is Nothing Then
    Set MBM_Loansdb = DAO.Workspaces(0).OpenDatabase(DB_Location)
End If