A way to provide your own adoRS object to an adoCMD Execute method? (1 Viewer)

[mdlueck]

Greetings,

I have used such syntax as in this snip below:

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.ADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_LocateProductByID"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@id").Value = Me.id
    Set adoRS = .Execute()
  End With

So the adoCMD object, which I very much enjoy the syntax of that object type, as it automatically builds the ADO.Parameters objects for me when it detects the SP on the BE DB and I make the .Parameters.Refresh call.

Question... must I receive back a brand new adoRS object from the call to the .Execute() method? Or is there some other adoCMD syntax where I can provide the adoRS object I wish the SQL SELECT result set to end up in?

What lacks in allowing the .Execute() method to build the adoRS object is the ability to specify attributes of the adoRS object.

And no, I am not interested in using an interpretive loop code to transfer data out of the automatically provided adoRS object to my own adoRS object.

I am thankful,

 

[sonic8]

Question... must I receive back a brand new adoRS object from the call to the .Execute() method? Or is there some other adoCMD syntax where I can provide the adoRS object I wish the SQL SELECT result set to end up in?

Remove the line Set adoRS = .Execute() from your code.
Then add:

Dim rs As AdoDb.Recordset
Set rs = new AdoDb.Recordset

set rs.ActiveCommand = adoCMD
rs.Open

Between creating the rs variable and opening it, you can set the properties of the Recordset, like CursorLocation, LockType and so on.

 

[mdlueck]

Thanks sonic8, that gave me enough of a memory jog. I arrived at this working syntax:

  Dim adoCMD As Object
  Dim adoRS As Object

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.ADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_RefreshLocalTmpTbl_All"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@projectid").Value = 8
  End With

  Set adoRS = CreateObject("ADODB.Recordset")
  With adoRS
    .ActiveConnection = Nothing
    .CursorLocation = adUseClient
    .Open Source:=adoCMD, _
          CursorType:=adOpenStatic, _
          Options:=adCmdStoredProc
  End With

So I am able to successfully define attributes such as this:
.CursorLocation = adUseClient
into the adoRS object.

For me, syntax:
set rs.ActiveCommand = adoCMD
kept blowing up citing some "not with block" reason. Annoying!

I am thankful,