Call SQL Server Stored Procedure from Excel VBA Code 1

[JoeZim]

A quick overview of how I'm getting Excel file data uploaded to our SQL Server:

1) A batch FTP process sends a copy of the spreadsheet to the server - I runt this .bat file with a Shell command.
2) A stored procedure on the server, using the FTP spreadsheet, then updates the SQL database. This stored procedure is setup and is accessed through the "Data-Get External Data-New Database Query - where I have a query setup in the "Choose Data source" Dialog box already.

What I'm trying to do is have this "Choose Data source" dialog box pop up so the user only has to select the query and hit OK. Better yet, I was hoping the VBA code could execute this query and the user would only need to key in their server password.

Hope this makes sense. We don't have to pull any data back to the Excel file. Thanks in advance!

Joe

 

[fredericofonseca]

The following is an example of how to execute a stored procedure on a server using ADO. No need to use the "Data-Get External Data-New Database Query "

You can also use this from Excel as long as you add the required references.

dim adoInsertComm as ADODB.Command
Set adoInsertComm = New ADODB.Command
adoInsertComm.ActiveConnection = DBConn
adoInsertComm.CommandType = adCmdStoredProc
adoInsertComm.CommandText = "sp_my_stored_procedure"
adoInsertComm.Execute


For further info search these forums/FAQ's for ADO

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JoeZim]

Thank you Frederico for the suggestion, it got me going in the right path. After some additional research and playing around, I finally got it to work!! Thanks again!

Joe