Ado Recordset from SP With Insert Update Ect

[PWise]

Can A recordset be returned from a Stored Procedure that starts with a Insert or Update Statement?

Public Cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Function ExecuteAdoRS(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
If Cnn.State = adStateClosed Then
    Cnn.ConnectionTimeout = 0
    Cnn.Open CurrentProject.Connection
End If
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
     cmd.Parameters(Prams) = AdoPrams(Prams)
Next Prams
Set ExecuteAdoRS = cmd.Execute(a)
If adoCommandType = 4 Then AdoPrams(0) = cmd(0)
End Function

I use the above code to return recordesets.

Sub xxxx()
Dim Rst As ADODB.Recordset
Set rst = ExecuteAdoRS("SPname",4,0,.....)

End Sub

Today, I am trying it on a new SP that has a couple of Insert and Update Statements when i try to run this codet it runs returns no errors does the updates ect but when i try to us the recordset

Rst.movefirst

i get this error

The object you entered is not a valid Recordset property.

 

[lameid]

Can A recordset be returned from a Stored Procedure that starts with a Insert or Update Statement?

There is no equivalent of looking at an action query in datasheet view to see what is going to get changed or appended.

If the stored procedure ends in a select statment that returns data, then it would work. You might insert data to a temp table and then select from it in one stored proc. Obviously your implementation should be more complicated like inserting data from multiple tables or inserting then joining to the temp table. This probably is not that practical in Access, but nonetheless possible.

 

[PWise]

Thanks Lameid for the reply.

I have already figured out how to get around my problem. I will try to post my solution in a separate post. First let me reply to you post.

There is no equivalent of looking at an action query in datasheet view to see what is going to get changed or appended.

What I wanted was not the equivalent of datasheet view of a Access action query. Rather as my question states

Can A recordset be returned from a Stored Procedure that starts with a Insert or Update Statement?

(Emphasis mine)

I.E.

Create procedure Procname

@Studentid int....


as

Create Table #TempTable (Fieldname.....)

insert into #TempTable 
Select .....
from ...
Where 

Update Table1
Set FieldName=xxxx
Where....

Select Field1,Field2...
from Table2
inner join #TempTable 
on...
left join Table1
on ....

Return

Using this code

Sub xxxx()
Dim Rst As ADODB.Recordset
Set rst = ExecuteAdoRS("Procname ",4,0,.....)
[COLOR=red]rst.movefirst [/color]
…..
End Sub

Will give this error

Operation is not allowed when the object is closed.

For this code

Sub xxxx()
Dim Rst As ADODB.Recordset
Set rst = ExecuteAdoRS("Procname ",4,0,.....)
[COLOR=red] Set me.recordset=rst  [/color]

…..
End Sub

You get this eror

The object you entered is not a valid Recordset property.

If you run this code (or any variation of this code )from QA EM or as a pass thru Querry in an Access .Mdb

Procname 25

Declare @Studentid int
Select @Studentid=25
 Exec procname @studentid

It returns rows

When you run it from A access .Adp

The Stored Procedure executed but did not return records successfully.

 

[danvlas]

Dim Rst As New ADODB.Recordset

should get you back on the saddle.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[danvlas]

Set me.recordset=rst"

Funny enough, form recordset is NOT an ADODB.Recordset, but some kind of DAO.Recordset... M$ misteries...






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PWise]

Thanks Danvals for replying to my post.

Dim Rst As New ADODB.Recordset

should get you back on the saddle.

I don't know what you mean by "should get you back on the saddle".
But, if you mean that if I add the NEW keyword to the declaration I should not get the error. I am sorry to report that even with the NEW Keyword I am still getting the same errors.

"Set me.recordset=rst"

Funny enough, form recordset is NOT an ADODB.Recordset, but some kind of DAO.Recordset... M$ misteries...

I am not sure what type of recordset a form recordset is but let me quote you from MSAccessHelp

The following example opens a form, opens a recordset, and then binds the form to the recordset by setting the form's Recordset property to the newly created Recordset object.

Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
End Sub

If you try this code also from access help it will return ADO Recordset

  If TypeOf me.recordset Is DAO.Recordset Then
        MsgBox "DAO Recordset"
    ElseIf TypeOf me.recordset is ADODB.Recordset Then
        MsgBox "ADO Recordset"
    End If