Excel 2007 VBA code to update linked-tables in Access 2003

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Hi armensng

Why run macro in Access, why not in Excel

I don't have file to test, but try this in Excel only

Please Login or Register  to view this content.

BrianM45: This is a valid point. lol. I had tried to run the code from Excel, but wasn't getting anywhere. I've adapted your code to fit my specific file.

However, I am getting an error on the following line with "Run-time error 424: Object Required".
Set Db = AccessObj.OpenCurrentDatabase(DbPath)

I can run without the Set command with no issue, but that prevents me from running the rest of the code:
AccessObj.OpenCurrentDatabase (DbPath)

Thanks again for your help.
Armen

Hi,

Have you changed the path and name of variable DbPath ?

Please Login or Register  to view this content.

It's an example

A+

Yup, the code has been updated for that. As mentioned, running the 2nd code does open the same DB, but trying to Set it as an object ("Db") seems to be the issue. I was double-checking by triggering a msgbox from access.