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

Question

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:\Users\example.dude\CloudDrive\Other Folders\Main Database.accdb
C:\Users\example.dude\CloudDrive\Other Folders\Archive 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:\Users\example.dude\CloudDrive\Other Folders\Archive Database.accdb\Archive 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.

"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." - you're going to run into synchronization issues because this won't be ACID compliant. You're going to need a a proper cloud-hosted database. — Dai
I understand the dangers and unfortunately this is the only solution available to me in my workplace. — Ford

Gustav Gustav · Accepted Answer · 2020-02-11T09:02:05

Use Environ:

INSERT INTO [Some Table] ( ID, [Other Fields] ) IN 'C:\Users\" & Environ("username") & "\CloudDrive\Other Folders\Archive Database.accdb\Archive Database.accdb'

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

2 Answers