View Full Version : Execute SQL Stored Procedure
mpadgett
02-13-2007, 12:19 PM
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
02-13-2007, 01:01 PM
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
02-13-2007, 02:49 PM
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
02-13-2007, 03:53 PM
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
02-14-2007, 01:42 PM
Thanks! Just what I needed.
Mike P.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.