Using Relative File Paths in Database

[DougTucker]

I need help changing hard-coded file paths to relative paths so my MS Access database will work on other networks or on a local PC. Please let me know if there’s a different forum where this should be posted (it’s specific to MS Access, but not particularly a VBA issue).

Here’s what I have now:
1. Macros use the TransferSpreadsheet command to pull files from hard-coded paths such as “I:\Department Files\DataConversionUtility\SourceFiles”.

2. Queries manipulate the data and format it for use by another application

3. Macros output the data to files (.txt and .xls) to a path such as “I:\Department Files\DataConversionUtility\OutputFiles”

This works great on our local network, but our coworkers in another country need to be able to use the utility. I can ship them a zipped MS Access database, but they don’t have access to our network, and won’t necessarily be able to map an “I:\...” drive as I have.

QUESTION: What is the proper way to create a relative path for the files so the application works properly on anyone’s PC or network?

I envision a structure that would require the user to create a “DataMigrationUtility” folder wherever the user wants it located:
[Path]\DataMigrationUtility\
This path would contain subfolders that the application could create the first time it is launched:
[Path]\DataMigrationUtility\OutputFiles
[Path]\DataMigrationUtility\SourceFiles

[Path]\DataMigrationUtility\Templates (etc…)

Thanks in advance for your help!


~ Doug T.

 

[lameid]

The short answer is you can't really do what you want. You can however make your code flexible to adapt to different installations...

You could have the database populate a record in a table with your first run information. The table should be local to the application. Then when the application loads up, it can populate global variables to use instead of the literals in code. You can even change the connection string of linked tables to point to a different location. Below is some code that does that... You will have to modify it for your purposes.

Sub SpecifyDBConnect(strNewpath as string)
    'Specifies database for target constant MDB
    'Useful if backend needs to be copied local for performance Reasons
    Const dbFile As String = "Backend.mdb"
    'strNewpath Path replacement
    
    Dim Ws As DAO.Workspace
    Dim Db As DAO.Database
    Dim tbls As DAO.TableDefs
    Dim tbl As DAO.TableDef
    Dim strconnect As String
    Dim intStartpos As Integer
    Dim intlenoldpath As Integer
    Set Ws = DBEngine.Workspaces(0)
    Set Db = Ws.OpenDatabase(strDBpath)
    Set tbls = Db.TableDefs
    
    If Right(strNewpath, 1) <> "\" Then
        strNewpath = strNewpath & "\"
    End If
    
    For Each tbl In tbls
        strconnect = tbl.Connect
        If Len(strconnect) > 0 Then
            intStartpos = InStr(1, strconnect, "DATABASE=", vbTextCompare) + Len("DATABASE=") - 1
            intlenoldpath = InStr(intStartpos, strconnect, dbFile, vbTextCompare) - intStartpos
            If intStartpos > 0 And intlenoldpath > 0 Then
                strconnect = Left(strconnect, (intStartpos)) & strNewpath & Right(strconnect, (Len(strconnect) - intStartpos - intlenoldpath + 1))
                'MsgBox tbl.Connect & Chr(13) & Chr(13) & strconnect
                tbl.Connect = strconnect
                tbl.RefreshLink
                tbls.Refresh
                'MsgBox tbl.Connect
            End If
        End If
    Next
    Db.Close
    Set tbl = Nothing
    Set tbls = Nothing
    Set Db = Nothing
    Set Ws = Nothing
End Sub

 

[lameid]

Another thing to consider, you can get the open file's path with...

currentproject.Path