Linked Table To Google Drive- Allow Multiple Users With Different File Paths To Run Queries, Access

[Sarkman22]

Hello All- I am relatively new with Access so I hope I am explaining this clearly. Please feel free to ask questions for clarification.

I am building a database that will have many linked tables to Excel files on Google Drive. I will be building Macros and Queries that other users will need to run, while accessing all of the files on Google Drive (currently the only platform we have for sharing documents).

The problem I have is that when I link a table, it uses my Google Drive path. When another user opens the database via Google Drive, it uses their user file path to Google Drive.

E.g.

I link the table and this is the path:
C:\Users\JSmith\Google Drive\Tables\SubTables\Table1.xlsx

My coworker tries to run a macro/query but it breaks because his file path is:
C:\Users\AWard\Google Drive\Tables\SubTables\Table1.xlsx


I had the same problem when writing a macro in Excel, but was able to solve it in VBA using this:
sPath = "C:\Users\" & Environ$("UserName") & "\Google Drive\Tables\SubTables\Table1.xlsx"

However, I don't know how to do this in Access (have the linked table use the "user name" in the linked path depending on who has the DB open). Not sure if this is simple or difficult so I apologize for my lack of knowledge here. Can I write a query to alter the path of the linked table or something?



Thank you for any help!

[June7]

I've never used 'the Cloud' so don't understand why pathing is referencing C: drive and User folders.

On a shared network file server I would use UNC pathing.

Is each user running their own copy of the frontend located on their local workstation?
Possibly modifying the table links with VBA will work for your situation. Here is my code:

Public Sub EditTableLinks()
'Use the variables provided to do the manipulations desired
Dim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
'replace the following strings as needed
strOld = "old path"
strNew = "new path"
Set db = CurrentDb
For Each td In db.TableDefs
    If InStr(td.Connect, strOld) > 0 Then
        Debug.Print td.Name
        Debug.Print "Old Link: " & td.Connect
        td.Connect = Replace(td.Connect, strOld, strNew)
        td.RefreshLink
        Debug.Print "New Link: " & td.Connect
    End If
Next td
db.TableDefs.Refresh
End Sub

[Sarkman22]

Thank you for your quick response! I will need to play around with this as I am new to the VBA coding. I appreciate the help...