Stored Proc from Access 1

[mlpaskett]

How can I call a stored procedure on SQL 2000 from an Access 2003 database?


Thanks--
Mark

 

[arst06d]

You can create a pass-through query in access which sends the correct sql commands eg exec storedprocname.
Look up sql specific queries in the help.

 

[danetjorosho]

This an example

Public Function GetNextOrdNo(cnn As ADODB.Connection) As String

Dim rst As ADODB.Recordset
Dim vResult As Variant
Dim oCmd As New ADODB.Command

GetNextOrdNo = "00000000"
With oCmd
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "GetNextOrderValue" ' name of stored procedure
' get parameter set from SP
.Parameters.Refresh
' set value of first parameter
'.Parameters("@InputData").Value = "10-37-3394"
.Execute
' get result from output parameter
vResult = .Parameters("@NextOrderValue").Value
End With
GetNextOrdNo = vResult
End Function