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:

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:

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

Hi armensng

Why run macro in Access, why not in Excel

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

Sub UpdateLink()
  Dim fd As FileDialog
  Dim sFilePath As String, DbPath As String, Tdf As Object
  Dim AccessObj As Object, Db As Object
  Dim tempStr As String
  
  ' Select file we want to use
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    If .Show = -1 Then sFilePath = .SelectedItems Else Exit Sub
  End With
  Set fd = Nothing

  ' Access base
  DbPath = "C:\Mybase.mdb"
  ' Create instance of Access
  Set AccessObj = CreateObject("Access.Application")
  AccessObj.Visible = False
  ' Open database
  Set Db = AccessObj.OpenCurrentDatabase(DbPath)
  '
  Set Tdf = Db.TableDefs("InvSpend")
  tempStr = "Text;DSN=InvSpend Link Specification1;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=" & sFilePath
  Tdf.Connect = tempStr
  Tdf.RefreshLink

  AccessObj.CloseCurrentDatabase
  AccessObj.Quit
  Set AccessObj = Nothing
  
End Sub

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 ?

DbPath = "C:\Mybase.mdb"

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.