calling SQL SP from Excel (macro)

[flashkarni]

hello all,
i am new to VB.
i am trying to execute SQL Stored procedures from a macro in excel.
but i fail to do so. i managed to write a code that imports a table, but not to execute a SP or a function.
heres my code:

Sub DataExtract()

' Create a connection object.
Dim cnPubs As ADODB.connection
Set cnPubs = New ADODB.connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=LENOVO-325FBA33\SQLEXPRESS;INITIAL CATALOG=StagingArena;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn
        


' Create a recordset object.
Dim rsPubs As ADODB.recordset
Set rsPubs = New ADODB.recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT * FROM Genres"
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsPubs
    
    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing


End Sub

i am working with ADO commands, and if anyone can direct me to the execute commands.
thanks,
barak

[vb5prgrmr]

If a SP returns some sort of results (T/F, recordset) then use a recordset as you are. If it just does something use a command object...

Good Luck

[flashkarni]

If a SP returns some sort of results (T/F, recordset) then use a recordset as you are. If it just does something use a command object...

Good Luck

thanks for the response, i am using a command object, and when i run the vb script i get a message saying: OLE DB command object is not supported by the provider. i am using SQL express 2005. any chance it does not allow for ADO commands?

[vb5prgrmr]

Don't think so, but then again I don't go up against 2k5 very often...

Now, in the example above, your select *..., if you are calling that a stored procedure, then you have your terminology wrong as that is a select statement and you need to use a recordset object pretty much as you are but if I remember correctly, there are some additional parameters missing from that open statement...

Good Luck