I am using Excel as the customer interface and they will need to change the connection string in the back end access database. I have built the function in Access - called Sub ChangeXLlinkConnection(ByVal FilePath As String, ByVal SheetName As String, strExcelTableName As String). Within Access, I can successfully change the links.

So... I am trying to provide the variables from excel and execute the link change from Excel. (FilePath, SheetName, and strExcelTableName are maintained in excel).

I have seen code such as...

Public Sub main2()
Dim strDBName As String
Dim FilePath As String
Dim SheetName As String
Dim strExcelTableName As String


strDBName = "C:\Test\Staging.accdb" 'Database that houses the linked file
FilePath = "C:\Test\Style Color.xls" 'Excel Workbook used to link
SheetName = "sheet1" 'Tab in Excel being linked
strExcelTableName = "tblMyData" 'Linked Access Table being changed

With CreateObject("Access.Application")
.OpenCurrentDatabase strDBName
.Run "ChangeXLlinkConnection" 'function within Access to be run
.Quit
End With
MsgBox "Done"
End Sub

With that said, the ".run..." line will fail. granted, I do not have the parameters shown but I when the were added, I had the same result.

Any help would be appreciated.

Thank you in advance.