Execute SQL Stored Procedure

[mpadgett]

I'm looking for some help in running a stored procedure in an SQL database. The procedure simply does an SQL INSERT to a table (adds a row) with variable values passed to it.

I want to execute this stored procedure from VBA in Excel with data from certain cells in the spreadsheet as the procedure's variable values.

I'm mainly needing help with syntax on making the connection to my SQL database and calling or executing the procedure.

Thanks - Mike P.

[Bob Phillips]

Use ADO.

For the connection string, see http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx

To run an SP, you
need the Command object. Google for ADO and Command.

[mpadgett]

Here's what I've tried. As you can see I placed the code behind a Command Button. When I click the button to issue the connection, I get an error.

Private Sub CommandButton1_Click()
oConn.Open "Provider=sqloledb;" & _
"Server=navision2;" & _
"Database=northwind;" & _
"User Id=excel;"
End Sub

Thanks - Mike P.

[Bob Phillips]

You have to create an ADO connection object



Private Sub CommandButton1_Click()
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=sqloledb;" & _
"Server=navision2;" & _
"Database=northwind;" & _
"User Id=excel;"
End Sub

[mpadgett]

Thanks! Just what I needed.

Mike P.