Running an Access function (with parameters) from Excel

  • Thread starter Thread starter csblakeatlb
  • Start date Start date
C

csblakeatlb

I am using Excel as the customer interface and they will need to change theconnection 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, Ican successfully change the links.

So... I am trying to provide the variables from excel and execute the linkchange 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.
 
Okay, Thanks Garry. I was actually successful with passing the variables. For everyone else, here is the code that worked...

Public Sub main2()
Dim strDBName As String
Dim strMessage As String
Dim FilePath As String
Dim SheetName As String
Dim strExcelTableName As String
Dim strRunString As String
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

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

Debug.Print strRunString

'*******************************************************************************
With objAccess
.OpenCurrentDatabase strDBName
.Run "ChangeXLlinkConnection", FilePath, SheetName, strExcelTableName
End With

'******************************************************************************
' clean-up
If Not objAccess.UserControl Then
objAccess.Quit
Set objAccess = Nothing
End If

'*******************************************************************************
MsgBox "Successfuly Completed! Happy Dance!!!! "
End Sub
 
Back
Top