UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> stored procedure problem    
 
   
rhodri
post Nov 12 2004, 06:00 AM
Post #1

UtterAccess Addict
Posts: 115
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
Go to the top of the page
 
+
rhodri
post Nov 12 2004, 08:02 AM
Post #2

UtterAccess Addict
Posts: 115
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?
Go to the top of the page
 
+
truittb
post Nov 12 2004, 08:08 AM
Post #3

Retired Moderator
Posts: 13,544
From: Texas (Is there anywhere else?)
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
Go to the top of the page
 
+
rhodri
post Nov 12 2004, 08:11 AM
Post #4

UtterAccess Addict
Posts: 115
problem solved thanks!
Go to the top of the page
 
+
truittb
post Nov 12 2004, 08:27 AM
Post #5

Retired Moderator
Posts: 13,544
From: Texas (Is there anywhere else?)
You are welcome.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 20th June 2013 - 07:00 AM