Re: Embedded SQL within VBA?

Without going too deeply into areas that I'm not actualy that familiar with anyway, try using

objRecordSet.MoveLast

before testing the recordcount.

If you can't MoveLast, then the provider you are using probably defaults to using an "adOpenForwardOnly" cursortype (you can use the CursorType parameter of the Open method to try to specify the CursorType, but the provider may change the type if it doesn't support the one you asked for. For example, the Access/Jet OLE DB provider doesn't (or didn't) support dynamic cursors and will give you a Keyset cursor. You can check the cursor type post-Open by looking at

objRecordSet.CursorType

If you have an adOpenForwardONly cursor type, try changing it to any of the others - if you need to be able to update the recordset, use adOpenDynamic or adOpenKeyset, otherwise you may be able to use adOpenStatic. Then, if necessary, try using .MoveLast.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Rick Charnes" <rickxyz--nospam.zyxbadcharnes@xxxxxxxxxxxxxxx> wrote in message news:MPG.2142271770b03b009899ab@xxxxxxxxxxxxxxxxxxxxxxx
I have my ADO connection working fine now. But can anyone help me
figure out why .RecordCount is returning -1 here? MyValue is assigned
correctly. Thanks.

Dim rstcount As Integer
Dim myvalue as string

objRecordset.Source = "SELECT * FROM mytable WHERE name = 'UE'"
objRecordset.Open

myvalue = objRecordset.Fields("gen_value")
rstcount = objRecordset.RecordCount 'returns -1



In article <OP1QwAm6HHA.5136@xxxxxxxxxxxxxxxxxxxx>,
pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx says...
> Am I asking VBA via ADO too much here?

No, but there may be a terminology gap if you are used to using SQL CURSORS
etc.

In ADO you would typically
a. open a Connection
b. open a RecordSet by specifying a COnnection, a piece of SQL, and a
couple of other parameters


.

Relevant Pages

  • Re: Embedded SQL within VBA?
    ... but RecordCount still returned -1. ... Setting CursorType to *adOpenStatic* did the trick and now I get my ... then the provider you are using probably defaults to ... or adOpenKeyset, otherwise you may be able to use adOpenStatic. ...
    (microsoft.public.word.vba.general)
  • Re: Embedded SQL within VBA?
    ... but RecordCount still returned -1. ... Setting CursorType to *adOpenStatic* did the trick and now I get my ... then the provider you are using probably defaults to ... or adOpenKeyset, otherwise you may be able to use adOpenStatic. ...
    (microsoft.public.word.vba.general)
  • Re: Multiple recordsets?
    ... Now that it's working just one other small issue that I'm encountering if I ... I tried using the MoveLast and MoveFirst but it gives me an error message ... CursorType to some of the other ones but it still doesn't allow the ... MoveLast/MoveFirst or get the full RecordCount. ...
    (microsoft.public.vb.database)
  • Re: Multiple recordsets?
    ... mySourceConn.CursorLocation = adUseClient ... loop since the RecordCount is coming up as -1. ... I tried using the MoveLast and MoveFirst but it gives me an error message ... Do you know if I should be using a different CursorType or another method ...
    (microsoft.public.vb.database)
  • Re: VB6 - ADODB - Strange behavior between IDE & EXE
    ... it mentions that RecordCount may return -1 for unsupported CursorType. ... However, this CursorType usage had been working for years, particularly with Access and Sybase ASE, on Windows 2000 and XP. ... It appears that the rs object itself is now reflecting some other object than that I set in the given LostFocus event. ... If iAns = vbYes Then ...
    (microsoft.public.vb.database.ado)