Member Avatar for 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

Edited by WaltP because: Fixed CODE Tags -- use the PREVIEW button before the SUBMIT button
  • 2 Contributors
  • 3 Replies
  • 107 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by vb5prgrmr
Member Avatar for 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

Member Avatar for 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?

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.