Access & SqlServer - Stored procedures, displaying data

[Guest]

Hi,

I have an mdb file, with tables linked to an sqlserver.
i have stored procedures defined in the SERVER.
i would like to know,
1. How can i run those procedures, from a form's event (vb)?
2. How can i define the record source of a report to display date returened
from that stored procedure? or how can i define a tabular report to display a
recordset?

Thanks

 

[Dan Artuso]

Hi,
I guess you could use either a pass-through query or use ADO to execute a command

Dim param As ADODB.Parameter
Dim cmd As ADODB.Command

Set param = New ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "[dbo].[InsertPatchGetId]"
cmd.CommandType = adCmdStoredProc

Set param = New ADODB.Parameter
param.Name = "patchName"
param.Type = adVarChar
param.Value = patchname
param.Size = 500
param.Direction = adParamInput
cmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Name = "patchid"
param.Type = adInteger
param.Direction = adParamOutput
cmd.Parameters.Append param

cmd.Execute

InsertPatchGetId = cmd.Parameters("patchid")

The above calls a stored proc named InsertPatchGetId and returns the id of the newly inserted patch.
You have to store the return value somewhere. I don't think there's any way to have a stored proc as a recordsource
but I may be wrong about that.
Oh yes, the above assumes cnn is a valid connection object.
You'll have to do some research on ADO and/or pass through queries.