Running an Access function (with parameters) from Excel

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.
 
S

Steve Blake

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
 
G

GS

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
 
S

Steve Blake

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.
 
G

GS

S

Steve Blake

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel crashes and restarts when accessing newly created worksheet 2
Evaluate text string as formula 1
VBA does not recognize Excel worksheet function "indirect" 2
Exporting multiple rows from access to excel, each in a new row 2
Copy data from Closed Workbook 3
Macro to copy from another workbook 1
error: Object doesnt support this property or method 2
Open second Excel file with VBA 5

Top