I thought this might be a good test for A.I. While this is obviously air code and may not work, it could spur an idea among humans
I have the same issue in python and I don't want to hardcode a long path with OneDrive, I was thinking a relative path might work. Anyway, ignore the "relative path" bit.
Me: I'm having trouble because OneDrive is in the path with some users but not with others.
A.I. If your Access database file is stored in a path that includes OneDrive for some users but not others, you may encounter issues with referencing files or folders using a relative path, since the path to the OneDrive folder can vary depending on the user's configuration.
One solution to this problem is to use a combination of the Environ function and string manipulation functions to construct the path to the file or folder based on the user's environment variables.
For example, you could use the following code to construct the path to a file named "MyFile.txt" located in a subdirectory named "MyFolder" of the parent directory of the user's OneDrive folder:
A.I. In this code, the Environ function is used to retrieve the value of the OneDrive environment variable, which contains the path to the user's OneDrive folder. The path is then manipulated using the Left and InStrRev functions to remove the "OneDrive" folder from the path and obtain the parent directory. Finally, the full path to the file is constructed by appending the relative path to the parent directory.
This approach should work regardless of whether the user's OneDrive folder is included in the path or not.
Code:
Dim oneDrivePath As String
Dim filePath As String
' Get the path to the OneDrive folder
oneDrivePath = Environ("OneDrive")
' Remove the "OneDrive" folder from the path to get the parent directory
oneDrivePath = Left(oneDrivePath, InStrRev(oneDrivePath, "\") - 1)
' Construct the full path to the file
filePath = oneDrivePath & "\MyFolder\MyFile.txt"