I have to confess I am not following exactly why a Command object is considered to be required here. ADO's object model allows us to work with any objects we actually need and it takes care of rest behind the stage.
However, here is a code that uses command and opens a recordset.
The only problem I have with that code above is that while I've succeeded in returning a client-side static type cursor, the locktype is overridden from explicitly stated 'adLockBatchOptimistic' to 'adLockReadOnly'.
On the other hand, if I chunk out the section for command and recordset and replace it with this:
I can get a fully updatable recordset.
Now that is not to suggest that Command object can't return a updatable recordset; it's possible that what I wrote in my code actually does different things because in latter example, even though I never use a Command object, it is implicitly created behind the scene and used to execute the statement I passed in the .Source property of the recordset object. Thus, I think it would be a good illustration of why being too explicit and insisting on controlling everything can only lead to frustration and aggravation.
With Connection, there's only CursorLocation, and Command has none of properties that are relevant for binding the properties. This is why the recordset object has to be set up before the Command object is referenced for executing the stored procedure into the recordset.
I wouldn't be surprised to find out that I bungled up in my first code example because this is kind of new to me. I usually don't bother with Command object whenever I need a recordset for binding a form, only using Command for whenever I want to perform an update operation that does not return any records. Even though the first example returns non-updatable recordset, it still succeeded in binding the form. I also observed that Command still returns a nonupdatable recordset even for a client-SQL statement.
However, here is a code that uses command and opens a recordset.
Code:
Dim x As ADODB.Connection
Dim c As ADODB.Command
Dim r As ADODB.Recordset
Dim l As Long
Set x = New ADODB.Connection
With x
.ConnectionString = "Data Source='XXXX'"
.CursorLocation = adUseClient
.Open
End With
Set c = New ADODB.Command
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient '3
.CursorType = adOpenStatic '3
.LockType = adLockBatchOptimistic '4
End With
With c
.ActiveConnection = x
.CommandType = adCmdStoredProc
.CommandText = "DoIt"
Set r = .Execute '(l, Array(0,999999))
End With
Set Me.Recordset = r
Debug.Print Me.Recordset.CursorLocation 'Should return 3
Debug.Print Me.Recordset.CursorType 'Should return 3
Debug.Print Me.Recordset.LockType 'Should return 3 or 4
Debug.Print Me.Recordset.State 'Should return 1The only problem I have with that code above is that while I've succeeded in returning a client-side static type cursor, the locktype is overridden from explicitly stated 'adLockBatchOptimistic' to 'adLockReadOnly'.
On the other hand, if I chunk out the section for command and recordset and replace it with this:
Code:
With r
.ActiveConnection = x
.CursorLocation = adUseClient '3
.CursorType = adOpenStatic '3
.LockType = adLockBatchOptimistic '4
.Source = "DoIt 0, 999999"
.Open
End WithI can get a fully updatable recordset.
Now that is not to suggest that Command object can't return a updatable recordset; it's possible that what I wrote in my code actually does different things because in latter example, even though I never use a Command object, it is implicitly created behind the scene and used to execute the statement I passed in the .Source property of the recordset object. Thus, I think it would be a good illustration of why being too explicit and insisting on controlling everything can only lead to frustration and aggravation.
With Connection, there's only CursorLocation, and Command has none of properties that are relevant for binding the properties. This is why the recordset object has to be set up before the Command object is referenced for executing the stored procedure into the recordset.
I wouldn't be surprised to find out that I bungled up in my first code example because this is kind of new to me. I usually don't bother with Command object whenever I need a recordset for binding a form, only using Command for whenever I want to perform an update operation that does not return any records. Even though the first example returns non-updatable recordset, it still succeeded in binding the form. I also observed that Command still returns a nonupdatable recordset even for a client-SQL statement.
