Solved %username% in MS Access? (1 Viewer)

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
I need to point to a file on each local machine that is in our C:\users\%username%\ directory, except that syntax isn't working. %username% works in Windows but apparently not in Access. Any ideas?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Feb 19, 2013
Messages
16,553
what is the code where you use %username%
 

Micron

AWF VIP
Local time
Today, 15:24
Joined
Oct 20, 2018
Messages
3,476
I'm not a big fan of Environ variables, but I seem to recall that there is one for the default user path. I think it's Environ("homepath")? If so, that ought to get you \Users\zeroaccess - is that what you're after?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:24
Joined
Feb 28, 2001
Messages
26,999
Sure. It is called "My Documents" (yes, with a space in the middle so may need quotes for proper use) which will get you where you want to put new documents.

Or you could do this with Environ() to find the folder where \Documents resides:

Code:
debug.Print Environ("HomeDrive")
C:
debug.Print Environ("HomePath")
\Users\Richard
 

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
I'm not a big fan of Environ variables, but I seem to recall that there is one for the default user path. I think it's Environ("homepath")? If so, that ought to get you \Users\zeroaccess - is that what you're after?
Yes - that appears to work. Now, what does the syntax look like as part of a full path?

Say the path for me is C:\users\zeroaccess\Work Folders\Documents
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:24
Joined
Feb 28, 2001
Messages
26,999
To see what environment variables are set in your system, open Settings. For Windows 10, in the search box type "Control Panel" without the quotes. (For other Windows systems, you are IN control panel immediately anyway.) Now select System and Security >> System. To the left look for "Advanced Settings" and click that. Now on the dialog box you just got click "Environment Variables." Browse away. If you don't see what you want, then Environ("X") won't get you there in one step. You might have to pick something that you know will be close and then tack on something via concatenation.

You could also look at generating your own local list using

 

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
To see what environment variables are set in your system, open Settings. For Windows 10, in the search box type "Control Panel" without the quotes. (For other Windows systems, you are IN control panel immediately anyway.) Now select System and Security >> System. To the left look for "Advanced Settings" and click that. Now on the dialog box you just got click "Environment Variables." Browse away. If you don't see what you want, then Environ("X") won't get you there in one step. You might have to pick something that you know will be close and then tack on something via concatenation.

You could also look at generating your own local list using

I think I do need concatenation to add a couple of folders on to the end. I fought with it this afternoon but I'm not good with the syntax. I'll have to come back to it later.
 

Micron

AWF VIP
Local time
Today, 15:24
Joined
Oct 20, 2018
Messages
3,476
maybe
"C:\" & Environ("HomePath") & "\Work Folders\Documents"

I tested with a system folder under my user name that has spaces (I never use them in file paths) and it seems to work without any special arrangement of quotes so Work Folders should be OK for you. If you named it that, I'd advise you to avoid the habit (including any non alpha or numeric characters even when allowed) in the future. Sooner or later the style will come back to bite you.

You can also replace C:\ with the "HomeDrive" variable.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:24
Joined
Feb 28, 2001
Messages
26,999
There is another possibility we didn't explore. Where is the database file? Because if it happens to be in the right folder, you could use CurrentDB.Name and from that string you could remove the name. If it happens to be in your \Work Folders\Documents folder, you are already there. Then all you need is an InStrRev to get the path. Now if that isn't where your DB is located, then of course that won't work.

Code:
strDBName = CurrentDB.Name
strDBPath = Left( strDBName, InStrRev( strDBName, "\" ) )

Just for clarity in case you weren't familiar with InStrRev, it finds the RIGHTMOST instance of \" in the string and tells you its position. The string returned this way would include that trailing "\" character.
 

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
maybe
"C:\" & Environ("HomePath") & "\Work Folders\Documents"
That seems to work, thank you! I dropped the first backslash, though.

In the immediate window:
msgbox "C:" & Environ("HomePath") & "\Work Folders\Documents"

Produces the proper path.

I tested with a system folder under my user name that has spaces (I never use them in file paths) and it seems to work without any special arrangement of quotes so Work Folders should be OK for you. If you named it that, I'd advise you to avoid the habit (including any non alpha or numeric characters even when allowed) in the future. Sooner or later the style will come back to bite you.

You can also replace C:\ with the "HomeDrive" variable.
Work Folders is a Windows concept and is how our computers are set up at work:

https://docs.microsoft.com/en-us/windows-server/storage/work-folders/work-folders-overview
 
Last edited:

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
There is another possibility we didn't explore. Where is the database file? Because if it happens to be in the right folder, you could use CurrentDB.Name and from that string you could remove the name. If it happens to be in your \Work Folders\Documents folder, you are already there. Then all you need is an InStrRev to get the path. Now if that isn't where your DB is located, then of course that won't work.

Code:
strDBName = CurrentDB.Name
strDBPath = Left( strDBName, InStrRev( strDBName, "\" ) )

Just for clarity in case you weren't familiar with InStrRev, it finds the RIGHTMOST instance of \" in the string and tells you its position. The string returned this way would include that trailing "\" character.
The front end will be in the aforementioned path, the only difference being that the actual user name will differ.

What I'm doing is a front-end auto-update, so this is the destination location of the file to copy from the network drive.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:24
Joined
Feb 28, 2001
Messages
26,999
If the Front End is in the right path, CurrentDB IS the front end and its .Name will contain that path.

You have to diddle with back-end table properties to find the path of the back-end file. Not that it is particularly hard, but from your comment in #12, it is right where you want it for my suggested method in #10.
 

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
If the Front End is in the right path, CurrentDB IS the front end and its .Name will contain that path.

You have to diddle with back-end table properties to find the path of the back-end file. Not that it is particularly hard, but from your comment in #12, it is right where you want it for my suggested method in #10.
That is an interesting method and would potentially work wherever the file is, offering a bit of future-proofing if locations change and saving me the trouble of having to come to the rescue. Am I reading that right?

If so, I like the idea. msgbox CurrentDB.Name just spits out the path...neat. And I do need the full path and file name for this purpose, so I don't think I need InStrRev but I'll remember it for the future. There may be some drawbacks but I'll have to do some testing and see how it goes. Thanks!
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 19:24
Joined
Jan 14, 2017
Messages
18,186
Two things that may be useful for future use if not the current situation:

1. The following code shows a list of all environment variables in the Immediate window
On my PC, there are 39 of them

Code:
Sub ShowEnviron()

'lists all envionment variables
    Dim strg As String
    Dim x As Long

    strg = "Environ Values 1  to 40" & vbCrLf & _
    "=======================" & vbCrLf
    For x = 1 To 40
        strg = strg & x & "  " & Environ(x) & vbCrLf
    Next x
    
    Debug.Print strg

End Sub

2. You can use CurrentProject to get the database name, folder path and full name (both combined)
For example, from the immediate window

Code:
?CurrentProject.Name
SDA.accdb
?CurrentProject.Path
C:\Programs\MendipDataSystems\SDA
?CurrentProject.FullName
C:\Programs\MendipDataSystems\SDA\SDA.accdb
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:24
Joined
Feb 28, 2001
Messages
26,999
Am I reading that right?

Yep, you are reading that right. If the DB file is in the same folder as the place you wanted to look for other files, my method works. In my case you use the InStrRev to remove the name but leave the path. On the other hand, Colin's post using CurrentProject as its base shows you how to find the path without the name.

There are so many ways to determine that location that I don't remember them all off-hand. Which is why you come here to get your answers. I will recall a few tricks of the trade offhand and someone else, in this case Colin, remembers a few more. Eventually, you get them all from the contributors.
 

zeroaccess

Active member
Local time
Today, 14:24
Joined
Jan 30, 2020
Messages
671
2. You can use CurrentProject to get the database name, folder path and full name (both combined)
For example, from the immediate window

Code:
?CurrentProject.Name
SDA.accdb
?CurrentProject.Path
C:\Programs\MendipDataSystems\SDA
?CurrentProject.FullName
C:\Programs\MendipDataSystems\SDA\SDA.accdb
So CurrentDB.Name and CurrentProject.Fullname give the same result for me.
 

isladogs

MVP / VIP
Local time
Today, 19:24
Joined
Jan 14, 2017
Messages
18,186
That's correct. You can use either.
However the additional two options I listed for currentproject have no direct equivalent
 

Users who are viewing this thread

Top Bottom