How to execute SQL Server store procedure from MS Access or VB

[Guest]

How do you execute/call SQL Server stored procedures from MS Access and
Visual Basic.

 

[Guest]

The following code should help you in running stored procedure from VBA:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

' If procedurename is "Proc" then

With cmd
Set .ActiveConnection = cnn

.CommandText = "Proc"
.CommandType = adCmdStoredProc

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

With rst
.CursorLocation = adUseClient
.Open cmd, , adOpenStatic, adLockReadOnly, adCmdStoredProc
End With
End With

Use the above method if your stored procedure returns a resultset.
For non-resultset returning stored Procedure,(eg. INSERT query) you may use
Execute method of command object to execute the procedure.