Locate google drive local path using VBA

[mor10]

In an Access database, I let users chose between many different cloud systems to store file attachments, while I just store a relative path in an Access table, e.g. MyAppFiles\123\filename.jpg, and it knows from the Cloud Service setting what cloud system to look for to get the complete path.

For a single user this is simple, but on a multiuser system the path various for each user, so a method to reliably locate the path is necessary.

I've been trying to find out how to locate the Google Drive for individual users using VBA. In the past, I simply looked for "C:\Users\" & Environ("UserName") & "\Google Drive", but this is not a reliable method. Has anyone had any success finding a way to do this?

 

[Gasman]

Perhaps inspect the registry?

 

[mor10]

I've looked everywhere in the registry as well as in all the files within the "Base Path": C:\Users\morte\AppData\Local\Google\DriveFS

 

[isladogs]

Have you checked whether there is an Environ value for this?

Sub ShowEnviron()

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

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

End Sub

I don't use GoogleDrive so am unable to test

 

[Gasman]

None on my computer?

 

[mor10]

Have you checked whether there is an Environ value for this?

Sub ShowEnviron()

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

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

End Sub

I don't use GoogleDrive so am unable to test

Tested, but no. Good suggestion though

 

[Gasman]

I certainly have the following ?

 

[mor10]

I certainly have the following ?
View attachment 103663

Me too, but it only has a link to the googledrivesync.exe executable, not the file storage path.

 

[Gasman]

Have you actually asked in any google forum?

 

[mor10]

No, I started here. I have asked Google support for documentation and help. But I will do.

 

[Gasman]

Try their community forums. You never know.

 

[bastanu]

Looks like you can overwrite the user's setting with the admin DefaultMountPoint setting:

New Drive File Stream settings for your organization’s deployment

Drive File Stream lets you stream files directly from the cloud to your computer and select files to be available offline. Based on your fee...

workspaceupdates.googleblog.com

Advanced Drive for desktop configuration - Google Workspace Admin Help

This article is for advanced users and administrators. To change common Drive for desktop settings, go here instead. You can set custom options for Google Drive for desktop, including the default dri

support.google.com

 

[mor10]

Looks like you can overwrite the user's setting with the admin DefaultMountPoint setting:

New Drive File Stream settings for your organization’s deployment

Drive File Stream lets you stream files directly from the cloud to your computer and select files to be available offline. Based on your fee...

workspaceupdates.googleblog.com

Advanced Drive for desktop configuration - Google Workspace Admin Help

This article is for advanced users and administrators. To change common Drive for desktop settings, go here instead. You can set custom options for Google Drive for desktop, including the default dri

support.google.com

That is a possibility, but they have added an additional folder layer, "My Drive". The user can't create anything in root location, but have to do it inside My Drive. It's possible that I have to have my users move to this new location to make it work, but first I will wait for potential answers to solve the problem without too much hassle.

 

[The_Doc_Man]

Wait a moment...

You are talking about file attachments and you named a .JPG (picture) file. Are you displaying a picture as part of (perhaps) a personnel database form / report and storing that picture on a cloud drive? If so, I wonder whether this would even be possible because of the implications of using Access file access methodology (SMB protocol) on a cloud server. If that attachment has to be used later by the Access DB, I would be worried about its stability. Not pointing fingers, just expressing concern.

 

[mor10]

No, no database on the cloud servers, only files like images, with a link to the file stored in the table. And Yes, I am showing the images in a form. That part works fine. It has worked for a long time using OneDrive, Dropbox, SharePoint, Box and Google Drive. It's just that Google has changed how things work on the Windows desktop recently.

 

[mor10]

Wait a moment...

You are talking about file attachments and you named a .JPG (picture) file. Are you displaying a picture as part of (perhaps) a personnel database form / report and storing that picture on a cloud drive? If so, I wonder whether this would even be possible because of the implications of using Access file access methodology (SMB protocol) on a cloud server. If that attachment has to be used later by the Access DB, I would be worried about its stability. Not pointing fingers, just expressing concern.

By the way, my users are using either an Access backend or a MySQL backend stored locally or remotely in our data center. All three works beautifully. The remote "hybrid cloud" solution became very popular when they had to vacate their offices early in the pandemic.

 

[The_Doc_Man]

If you are happy then fine. I'm just a skeptic of any sentence that contains the words "Access" and "cloud" together.

 

[mor10]

If you are happy then fine. I'm just a skeptic of any sentence that contains the words "Access" and "cloud" together.

Our cloud solution keeps the Access front end locally and a MySQL database remotely. Works fine.