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.

Update:

I was successful in the following code but now need to pass the parameters and have NOT been successful. The new function "RunIT" (See below) was created to pass the parameters from within Access.

Sub main3()
Dim strDBName As String
Dim strMessage As String
Dim FilePath As String
Dim SheetName As String
Dim strExcelTableName As String

strDBName = ThisWorkbook.Path & "\" & "Staging.accdb"
FilePath = ThisWorkbook.Path & "\" & "Style Color.xls"
SheetName = "MA DATA"
strExcelTableName = "tblStyleColor"

With CreateObject("Access.Application")
.OpenCurrentDatabase strDBName
.Run "RunIT"
.Quit
End With

MsgBox "Done"
End Sub

Have you tried just running the processes entirely within Excel? You
don't need links because ADODB will work for this nicely, even to run
stored procedures in your Access db!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks Garry, I like were you are going there. Can you or anyone, provide some link(s) to direct me to the process of making that happen? I have never done this and would appreciate some direction.

Thanks again.

Thanks Garry, I like were you are going there. Can you or anyone, provide
some link(s) to direct me to the process of making that happen? I have never
done this and would appreciate some direction.

Thanks again.

Here's a good place to start...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Nice job! Thanks for the feedback...

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion