What is the syntax to call stored procedure in SQL Server from Excel (VBA) using windows authentication?
Add a comment
|
1 Answer
'Remember to reference Microsoft ActiveX Data Objects via Tools - References
Dim strConn As String
Dim conn As New Connection
Dim cmd As New ADODB.Command
Dim rs As New Recordset
strConn = "DRIVER=SQL Server;SERVER=ServerName;DATABASE=DBname"
conn.ConnectionString = strConn
conn.Open
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SPName"
cmd.Parameters.Refresh 'requires a trip to server / can add parameters manually using cmd.Parameters.Append
cmd.Parameters("@Param1").Value = ""
Set rs = cmd.Execute
If Not rs.EOF Then
'your code here
End If
conn.Close
Set rs = Nothing
