How do I reference a user profile path from within Microsoft Access SQL INSERT INTO statement?

I’m using a cloud storage drive to share some complicated Access databases with my coworkers. I’d like the databases to execute directly from the local cloud storage path.

C:Usersexample.dudeCloudDriveOther FoldersMain Database.accdb
C:Usersexample.dudeCloudDriveOther FoldersArchive Database.accdb

I’ve been able to get most of it to work. However I have some append queries in the Main Database that add data to the Archive Database. Not the full SQL statement below, just an example.

::: Query in Main Database :::

INSERT INTO [Some Table] ( ID, [Other Fields] ) IN 'C:Usersexample.dudeCloudDriveOther FoldersArchive Database.accdbArchive Database.accdb'
SELECT [Some Table].ID, [Some Table].[Other Fields] AS etc etc etc

The problem is that “example.dude” is the user profile path of just one user. How do I access the OS %USERPROFILE% in a SQL statement?

I’m averse to some sort of VBA solution that dynamically recreates the SQL; I have at least a dozen of these SQL statements and some of them are VERY lengthy.

Answer

Not an answer to this exact question, but I did find a workaround.

I used the ‘subst’ command in Windows command line to map a drive letter to %userprofile%CloudDriveetc

This command isnt persistent, so I created a task in task scheduler to run at user logon and run subst.

Then all I had to do was change SQL to point to new drive letter and path.