Use Access forms as a front end to SQL Server

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.
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 1

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:
Code:
With r
    .ActiveConnection = x
    .CursorLocation = adUseClient       '3
    .CursorType = adOpenStatic          '3
    .LockType = adLockBatchOptimistic   '4
    .Source = "DoIt 0, 999999"
    .Open
End With

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.
 
rst.Open "EXEC SPName", connObject, adOpenKeyset, adLockOptimistic

Hmmm. I would be inclined to think there's something else that may not be related to the syntax being used.

Have you tried Leigh's suggestion of switching out the driver? What does your Connection object have for Provider property? Are you using a DSN or DSN-less connection? (Not that it'd make any difference but may be relevant to questions whether we have all required arguments being given)

Also, this is off the wall, but what are your rights to the server?

This recordset is full of data because it POPULATES MY LOCAL TABLE and, hence, the subform

Hmm, I don't know about that. See, if it was really a forward only recordset you should have had gotten an error because forward only recordset can't be bound to the form. You may have actually got a client-side static read-only recordset if you could bind it to the form.

As I said before, ADO will change properties to whatever is legal. In my first code example, I explicitly set locktype for optimistic locking, but ADO changed it implicitly to read only lock because that was legal for reasons unknown to me as of yet.
 
OK at the risk of complicating the issue now by replying with so much going on I'll post some thoughts and then am going to leave it for a bit.

>> "I have to confess I am not following exactly why a Command object is considered to be required here"

It isn't really - not for the recordset opening per se. It's an implementation that Valery is using across the board.
It's absolutely required in the action statements though.


>> 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'.

In your code you opened the recordset with:
Set r = .Execute '(l, Array(0,999999))
Even a client side cursor can't save you from that being the default when so executed.


>> Hmmm. I would be inclined to think there's something else that may not be related to the syntax being used.

Exactly. That was the exact point of the test which I'd asked for.
We can now reasonably point to using the SP as the source being cause of the cursor type.


Valery, glad you had some success (albeit only in testing a SQL statement which you can't use).
When you said
"I included sp definition in my previous post"
bear in mind that you then replied with:
">>>>> Set NoCount On
This line is included in all stored procedures."

Well - if that was the full definition you'd posted previously - I'd have known that it had "Set NoCount On" present.
Precision is important. When questioners miss out what they don't think is important to us, that has the immediate assumption that they're in the position to know what's important. And if that's the case then why ask at all? ;-)
Do you see what I mean? I'm not criticising, but just explaining why I ask for things.


I wouldn't expect permissions to come in to this - as far as cursor types etc.
(The updatability of the result set by all means - but not the functionality).

Does this happen catagorically on all of your SPs?
Have you tested on another one?
I have known deleting and then recreating an SP to make a difference.


Cheers.
 
>> "I have to confess I am not following exactly why a Command object is considered to be required here"

... It's an implementation that Valery is using across the board.

Which was why I asked that question, because prior to Valery's post this AM, Valery seemed to be using Command objects and that concerned me somewhat because it's not usual method to fetch a recordset that's intended to be updatable. Mind, I don't know enough if it's wrong, only concerned that it may be unnecessarily complicating things.

In your code you opened the recordset with:
Set r = .Execute '(l, Array(0,999999))
Even a client side cursor can't save you from that being the default when so executed.

The help on that method did not specify how recordset would behave in such instances, which may have had contributed to the confusion. I would have had expected the help to explicitly indicate that it would be coerced to read-only recordset.

But to be 100% sure, though, are you saying it rules out using Execute method of the Command object if the objective is to return a updatable recordset?
 
About to pop out so this will definitely be my last post for a while ;-)

I wouldn't say that using a command object is at all unusual for opening a recordset.
It's simply the rigorous way. It's likely easier to implement as a standard procedure too - including or passing parameters as a collection or whatever, emulating ADO's own OM of the command.
And as mentioned - it is the only way to retrieve output parameters - which are perfectly reasonable even when fetching a recordset.


>> "But to be 100% sure, though, are you saying it rules out using Execute method of the Command object if the objective is to return a updatable recordset?"

The Execute method in general to open a recordset will always result in a Read Only recordset.
It will result in a Forward only recordset too - as that's the default... unless you have a client side cursor. (The reason for that is because you can only have a Static cursor type with a client side cursor - so ForwardOnly isn't an option. Otherwise it would default to that.)

i.e. Execute always equals ForwardOnly, ReadOnly - unless client side in which case by necessity it must be Static, ReadOnly.

Cheers.
 
Sorry - to actually answer your question... :-)

Yes - it rules out the Execute method for a command object. :-)
But there's no need to execute a command. Supplying it as the source object will implicitly execute it to serve the recordset.

i.e.
rst.Open cmd
is fine alone once the command is properly set up. There's no need to explicitly execute it first
cmd.Execute
rst.Open cmd

But
Set rst = cmd.Execute
always results in read only (and Forward Only if not client side).

Cheers.
 
Cool. Thanks for the clarification, Leigh.

So if I wanted to receive a output parameter and an updatabale recordset:

Code:
cmd.Execute l, Array(x, y, z)
rst.Open cmd

'Read the output parameter from parameter objects...

Seem to me that should have been documented. (or it was, but I didn't see it/looked in right place) Oh well.

Nonetheless, glad to learn a new trick with ADO.
 
True - if you're wanting to pass parameters like that then yes. Otherwise there's no need to execute it first.
I'd pretty much always use the parameters collection personally. I just find it neater and more controlled.

Cheers!
 
Thanks again, Leigh. :)

Funny that. I personally thought ADO's parameters were kind of awkward in that you have to Append a CreateParameter..., whereas DAO is more straightforward and I came to like the idea of Array() in the Execute or passing it embedded in the Source property as a shortcut to avoid the lengthy and wordy CreateParameter.
 
It's true - as with other things - that ADO has shortcuts to methods.
I remember feeling quite daunted when I first saw ADO's OM and the comparatively involved process of establishing a command object. The thought of doing so repeatedly for each request seemed excessive. (It was quite some time ago to be fair - I was probably a relative noob ;-)

It's in its nature to offer alternatives to how properties and objects are added.
I tend to emulate that in any functional objects I create now (i.e. allowing parameters to be passed to a method - or established previously and then the method just run).

To be fair, ADO doesn't demand that dedicated parameters are appended as in the classic CreateParameter example. You can refer to the parameters merely as an available collection as you would in DAO if you're prepared to accept the server round trip of refreshing that collection first. (Not such a trip in Jet apps of course ;-)
The collection will actually refresh impicitly if you just refer to a parameter
cmd.Parameters(0).Value = "SomeVal"
but it's still that same round trip to the server to check the definition first as if you'd used
cmd.Parameters.Refresh
cmd.Parameters(0).Value = "SomeVal"
cmd.Parameters(1).Value = 123
'etc

The Array is indeed a simple method, and concatenating the Exec string even simpler really. All part of the versatility. Even though it took until ADO 2.6 before it was "versatile" enough that you didn't have to add the parameters in ordinal position rather than name. ;-)

Cheers.
 
It's Sunday and I have a lot of chores to do risking my wife's anger (-:
I will look at all in detail later.
Banana, just a simple test: forward-only recordset 'understands' only rst.MoveNext. If you try rst.MoveLast or rst.MoveFirst it fires an error. Can you test the recordset openned via command with these lines?
 
Valery:

Here's the code that you can copy and paste and test it yourself. It probably didn't cover all the cases but hopefully enough to illustrate the influence different syntaxes and how defaults are specified can have on what the result are.

A major caveat though. The result is likely dependent on the provider's capabilities. I used MSDASQL provider, but if we're using a different provider the result may be slightly different. However, the choice of provider shouldn't be influenced by the syntax difference between this:

Code:
cmd.Execute
rs.Open cmd

and

Code:
Set rs = cmd.Execute

The latter will always return either a server side forward only recordset or a client side static recordset while former will depend more on what provider is capable of providing, overriding defaults where it cannot satisfy the request.


Anyway, feel free to drop this code in a form's Open event or if you prefer a button's click event. Whatever floats your boat. If you don't want to test on a form, comment out all lines "Set Me.Recordset = r".
Code:
10    On Error GoTo errhandler

      Dim x As ADODB.Connection
      Dim c As ADODB.Command
      Dim r As ADODB.Recordset
      Dim l As Long
      Dim p As Variant

      Const cn As String = "DSN=XXX;"
      Const sp As String = "Doit"
20    p = Array(0, 999999)

30    Set x = New ADODB.Connection

      'No proeprties specified; using defaults.
40    With x
50        .ConnectionString = cn
60        .Open
70    End With

80    Set c = New Command
90    Set r = New ADODB.Recordset

100   With c
110       .ActiveConnection = x
120       .CommandType = adCmdStoredProc
130       .CommandText = sp
140       Set r = .Execute(l, p)
150   End With

      'Will not succeed.
160   r.MoveLast
170   r.MoveFirst
180   Set Me.Recordset = r

      'Will return a server side forward only, read only recordset
190   Debug.Print r.CursorLocation 'Should return 2
200   Debug.Print r.CursorType     'Should return 0
210   Debug.Print r.LockType       'Should return 1
220   Debug.Print r.State          'Should return 1

      'Now specifying client-side cursor.
230   r.Close
      'NOTE: Take note where we change connection
      '      object's location, rather than recordset object.
      '      The rest of code manipulates the
      '      location using recordset object.
240   x.CursorLocation = adUseClient

250   With c
260       .CommandType = adCmdStoredProc
270       .CommandText = sp
280       Set r = .Execute(l, p)
290   End With

      'Will succeed.
300   r.MoveLast
310   r.MoveFirst
320   Set Me.Recordset = r

330   Debug.Print r.CursorLocation 'Should return 3
340   Debug.Print r.CursorType     'Should return 3
350   Debug.Print r.LockType       'Should return 1
360   Debug.Print r.State          'Should return 1

      'Now explicitly setting for keyset cursor but
      'still using the same method of r = c.Execute
370   r.Close
380   x.CursorLocation = adUseServer
390   r.CursorType = adOpenKeyset

400   With c
410       .CommandType = adCmdStoredProc
420       .CommandText = sp
430       Set r = .Execute(l, p)
440   End With

      'This will fail because recordset is coerced to forward only.
450   r.MoveLast
460   r.MoveFirst
470   Set Me.Recordset = r

480   Debug.Print r.CursorLocation 'Should return 2
490   Debug.Print r.CursorType     'Should return 0
500   Debug.Print r.LockType       'Should return 1
510   Debug.Print r.State          'Should return 1

      'Now to attempt the same default but this time do not use
      'the syntax of set r = c.Execute

520   r.Close
530   r.CursorLocation = adUseServer
540   r.CursorType = adOpenKeyset

550   With c
560       .CommandType = adCmdStoredProc
570       .CommandText = sp
580       .Execute l, p
590       r.Open c
600   End With

      'The recordset was coerced to server-side static recordset.
      'Movelast shouldn't raise an error but it does anyway.
610   r.MoveLast
620   r.MoveFirst
630   Set Me.Recordset = r

640   Debug.Print r.CursorLocation 'Should return 2
650   Debug.Print r.CursorType     'Should return 3
660   Debug.Print r.LockType       'Should return 1
670   Debug.Print r.State          'Should return 1

      'Now to specify for client-side static to bind recordset.
680   r.Close
690   r.CursorLocation = adUseClient
700   r.CursorType = adOpenStatic
710   r.LockType = adLockBatchOptimistic

720   With c
730       .CommandType = adCmdStoredProc
740       .CommandText = sp
750       .Execute l, p
760       r.Open c
770   End With

      'This will succeed.
780   r.MoveLast
790   r.MoveFirst
800   Set Me.Recordset = r

810   Debug.Print r.CursorLocation 'Should return 3
820   Debug.Print r.CursorType     'Should return 3
830   Debug.Print r.LockType       'Should return 4
840   Debug.Print r.State          'Should return 1

850   r.Close
860   x.Close

870   Set r = Nothing
880   Set c = Nothing
890   Set x = Nothing

900   Exit Sub

errhandler:

910   Debug.Print Erl & ": " & Err.Number & ": " & Err.Description
920   Resume Next
 
Just to point out - we've suggested other providers.
We've never even seen the connection you're currently using.
What is "connSql" in your original code?

I might not be around to follow this up for a few days.

Cheers.
 
Thanks Banana!
I modified my code according to your suggestions. And this one works:
cmd.CommandText = "sp_GetReviewCriteria"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = lngCase

If rsDb.State = adStateOpen Then rsDb.Close
rsDb.CursorLocation = adUseClient
rsDb.CursorType = adOpenKeyset
cmd.Execute

rsDb.Open cmd
If Not rsDb.EOF Then
rsDb.MoveNext
rsDb.MoveFirst
MsgBox rsDb.RecordCount <<<== this one works (that indicates cursor location on client


>>> rsDb was declared and set at the top of the code
>>>> cmd - command object (and its active connection) were also set before
So thank you and others for very interesting and helpful discussion. And ... just because "one fool can ask more questions than 100 wise men can answer (Chinese proverb)" expect more questions from me (-:
 
I'm glad you worked out a solution. :)

Just as a FYI:

Properties such as CursorLocation, CursorType and LockType are both read/write so you can check the properties instead of using message box or invoking a method. The only thing is that you have to be sure you are reading them at right time. Reading them right after you've specified them mostly won't help but reading it after it has been opened/executed will provide answers whether ADO was able to satisfy your specifications or change some of them because of limitations/syntax/circumstances.

Good luck.
 
Thanks again. I am aware of properties of ADO recordset - I used it a lot when working with client/server and web applications. Now with Access...
Have more challenges (this time not with ADO). Probably will start a new post (not today) )-:
 
Just to mention your line
rsDb.CursorType = adOpenKeyset
will be impossible to enforce, ADO will return a Static cursor type instead. This is because that is the only cursor type that a client side cursor supports.

Cheers.
 
I'm also using refresh method.
I had an impression that in parameters collection parameters(0) is reserved for Return value
For example:
********** stored procedure ********
Create PROCEDURE [dbo].[sp_InsertKeyIssues]
@KeyIssuesText
nvarchar (250),
@ClinicalReviewID int


AS
BEGIN
SET
NOCOUNT ON;
Insert into tblKeyIssue
(KeyIssuesText ,ClinicalReviewID)
Values
(@KeyIssuesText ,@ClinicalReviewID)
return @@rowcount
END

************ VBA code ***********
cmd.ActiveConnection = connSql
cmd.CommandText = "sp_InsertKeyIssue"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(0).Direction = adParamReturnValue
cmd.Parameters(1).Value = strKeyIssueText
cmd.Parameters(2).Value = lngCase
cmd.Execute
intRetValue = cmd.Parameters(0).Value
If intRetValue <> 1 Then
MsgBox "Record was not inserted"
Else ....

and so on





It's true - as with other things - that ADO has shortcuts to methods.
I remember feeling quite daunted when I first saw ADO's OM and the comparatively involved process of establishing a command object. The thought of doing so repeatedly for each request seemed excessive. (It was quite some time ago to be fair - I was probably a relative noob ;-)

It's in its nature to offer alternatives to how properties and objects are added.
I tend to emulate that in any functional objects I create now (i.e. allowing parameters to be passed to a method - or established previously and then the method just run).

To be fair, ADO doesn't demand that dedicated parameters are appended as in the classic CreateParameter example. You can refer to the parameters merely as an available collection as you would in DAO if you're prepared to accept the server round trip of refreshing that collection first. (Not such a trip in Jet apps of course ;-)
The collection will actually refresh impicitly if you just refer to a parameter
cmd.Parameters(0).Value = "SomeVal"
but it's still that same round trip to the server to check the definition first as if you'd used
cmd.Parameters.Refresh
cmd.Parameters(0).Value = "SomeVal"
cmd.Parameters(1).Value = 123
'etc

The Array is indeed a simple method, and concatenating the Exec string even simpler really. All part of the versatility. Even though it took until ADO 2.6 before it was "versatile" enough that you didn't have to add the parameters in ordinal position rather than name. ;-)

Cheers.
 

Users who are viewing this thread

Back
Top Bottom