Hey all. Long time reader, first time poster.

My question:
Using VBA: From Excel 2007, I need to update a linked-table within Access 2003 for both the filename of the data it is linked to, and the path it is linking through.

My setup so far:
1) In Access, I started by manually linking my data. (data is: InvSpend.tab)
2) I created a module with a function to update the path based on where the DB was stored in case it was moved around. (the initial ask on this project)
Formula: copy to clipboard 
Public Function updateLink() As String

    Dim tempStr As String



    Set db = CurrentDb

    Set tdf = db.TableDefs("InvSpend")

    

    tempStr = "Text;DSN=InvSpend Link Specification1;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=" & CurrentProject.Path

    

    tdf.Connect = tempStr

    tdf.RefreshLink

    

End Function

3) I created a macro in Access to run this code. (called it "updateLink")
4) In Excel, I successfully ran the macro in Access with the following code:
Formula: copy to clipboard 
Set accessObj = CreateObject("Access.Application")

accessObj.Visible = False

accessObj.OpenCurrentDatabase (dbPath)

accessObj.DoCmd.RunMacro "updateLink"

accessObj.CloseCurrentDatabase

accessObj.Quit

Set accessObj = Nothing


My issue:
The filename and location of the linked-table in Access will change periodically. I need to find a way to either pass variables from Excel to the Access function, or have Access read cells within Excel to make that update. (preferrably the former choice). Also to note: The file will not be in the same directory as the database, and will still be accessed over the network. (CurrentProject.Path won't work). I have seen some code online to pass parameters to queries, but none to a macro or module-function to update a linked-table.

Apologies for the verbose post, I hope I have outlined my progress and where I've gotten stuck. Any help or alternate methods appreciated.

Best,
Armen