How to build a variable filepath as link or path in MS ACCESS using VBA

I have the following problem in MS ACCESS:

Using VBA, a PDF-file is stored in the OneDrive folder and synchronized with Sharepoint. As known, OneDrive is declared as a kind of another drive on all computers in the same way.

After saving, the file is located on computer01 in the folder:

C:\Users\User01\OneDrive - AllFolders\SharedFolder\AllDocs\Doc01.pdf =>( \USER01 )

Now user02 on computer02 who is also connected to the folder wants to open the file via VBA. The path of the file in the shared table field is accordingly:

C:\Users\User01\OneDrive - AllFolders\SharedFolder\AllDocs\Doc01.pdf

Of course User02 can't open the file via VBA, because the path should be:

C:\Users\User02\OneDrive - AllFolders\SharedFolder\AllDocs\Doc01.pdf =>( \USER02 )

For example I tried to use such a path to save the file (this is not the correct Syntax!):

(Environ("USERPROFILE")) & "\OneDrive - AllFolders\SharedFolder\AllDocs\Doc01.pdf

but (Environ("USERPROFILE")) as table.field.value is of course understood as string and not as variable!

How can this problem be solved? Thanks!

my Code:

Option Compare Database
Option Explicit

Public OneDriveDirectory As String

Public Function SetPathToOneDrive() As String
    
SetPathToOneDrive = (Environ("USERPROFILE")) & "\OneDrive - AllFolders\SharedFolder\AllDocs\"

End Function

Sub SaveDoc()

Dim xSource As String, xDestination As String, xFilename As String    
Dim FSO As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset

OneDriveDirectory = SetPathToOneDrive

xFilename = "Doc01.pdf"
xSource = (Environ("USERPROFILE")) & "\" & xFilename
xDestination = OneDriveDirectory & xFilename

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDocLinks", dbOpenDynaset)

Set FSO = VBA.CreateObject("Scripting.FileSystemObject")

Call FSO.CopyFile(xSource, xDestination)

   With rs
     .AddNew
     .Fields("fldDocPath") = xDestination
     .Update
   End With

rs.close
db.close
    
End Sub

Solution

With

SetPathToOneDrive = Environ$("%UserProfile%") & "\OneDrive - All Folders\SharedFolder\AllDocs\"

you should get the correct path.

Are you sure the path is correct? The default would be something like C:\Users\username\OneDrive\...

Depending on the environment it's probably not a good idea to store absolute filepaths in the database. I tend to save only the filename and build the path together in VBA, often using values grabbed from a config-table or -file.