UtterAccess Discussion Forums > stored procedure problem

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

rhodri

Nov 12 2004, 06:00 AM

I can use the following code to call a "select * from...." stored procedure from VBA

Dim cmd as ADODB.Command
Dim paramRegionCode As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramRegionCode = New ADODB.Parameter
With paramRegionCode
.Name = "@my_param"
.Type = adVarChar
.Size = 2
.Value = rs!parameter
End With
With cmd
.Parameters.Append paramRegionCode
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "my_stored_proc"
.Execute
End With

After I call the stored procedure, i need to know if any results were found or not - i.e. a true or false value. (The same thing as using "if rs.EOF = False then..." with recordsets)

Is this possible? How?

Thanks

rhodri

Nov 12 2004, 08:02 AM

does anybody know of a way of calling a stored procedure and passing a parameter to it in vb, and opening it as a recordset?

truittb

Nov 12 2004, 08:08 AM

Add a Recordset to the code.

CODE

Dim cmd as ADODB.Command
Dim paramRegionCode As ADODB.Parameter
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set paramRegionCode = New ADODB.Parameter
With paramRegionCode
.Name = "@my_param"
.Type = adVarChar
.Size = 2
.Value = rs!parameter
End With
With cmd
.Parameters.Append paramRegionCode
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "my_stored_proc"
End With
    With rs
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .CursorLocation = adUseServer
        .Open cmd
        If Not .EOF and Not .BOF Then
               'You have Records
        Else
               'You Don't
        End IF
        .Close
    End With

rhodri

Nov 12 2004, 08:11 AM

problem solved thanks!

truittb

Nov 12 2004, 08:27 AM

You are welcome.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.