How to use Excel VBA program to execute SQL SERVER stored procedur

[Arron]

Dear all,
I want to use Excel VBA program to execute SQL SERVER stored procedure.
But error occurs."Error sql statement ,except Insert update delete procedure"
How can I correct this ? thanks

code:
sqlstr = "exec stored_proc_name('Parameter')"
Sqldb.Execute sqlstr

 

[Madhan]

Hi, in my opinion, you have to initialise the Sqldb with valid
authentication, connection string/DSN etc.... and then try running the
statement.

 

[urkec]

I have some sample code that executes sp_dboption to set quoted identifiers
on for Pubs, then displays all set options for Pubs database. Hope it helps
some.

Sub testSP()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "ServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = _
"sp_dboption ('pubs','quoted identifier','on')"
cmd.CommandType = adCmdStoredProc

Set rs = cmd.Execute

cmd.CommandText = "sp_dboption ('pubs')"

Set rs = cmd.Execute

rs.MoveFirst

Do While Not rs.EOF
For Each f In rs.Fields
Debug.Print f.Name & vbTab & f.Value
Next
rs.MoveNext
Loop

rs.Close
cn.Close

End Sub