We help IT Professionals succeed at work.

VBA ADODB Getting a record count using RecordSet

The following code connects and runs with no errors, but not getting the expected result

Dim strCon As String
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=shaft)(PORT=1521))" & _
"(CONNECT_DATA=(SID=WM))); uid=ball; pwd=sack;"
Sql = "SELECT * FROM tblMyTable"
Set rs = conn.Execute(Sql)
If rs.RecordCount > 0 Then
 'do something
End If

Open in new window


The value of rs.RecordCount is -1 but there are 11 records in the table. What am I doing wrong here?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
A quick search on this seems to say that you need:
rs.CursorLocation = adUseClient


From many sources.  For example:
http://www.adopenstatic.com/faq/recordcounterror.asp
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Try adding this just before the if statement.
rs.MoveLast
rs.MoveFirst

Open in new window

CERTIFIED EXPERT

Commented:
you actually need to navigate in the loop.

by executing the statement you  will be in the first block of the result set, you also need to have rs.MoveNext in your code to move into the next record

see the sample code for illustration here, check for the adodb record set navigation example in second half of this page

http://www.accessallinone.com/looping-through-a-recordset/
Mike MillerSoftware Engineer

Author

Commented:
rs.CursorLocation = adUseClient still returns -1
also tried rs.CursorType = adOpenDynamic from another article...same result


rs.MoveLast
rs.MoveFirst
returns the following: Rowset does not support fetching backward


Wasim, I'm not even getting past the if statement. I'll work on the loop once I do. This is some old code that someone else developed. I just changed the method used to connect. I assume the logic still works fine. We'll see. Need to actually get into the loop first.
Software Engineer
Commented:
Got it...

rs.Open Sql, ObjConn, adOpenKeyset, adLockPessimistic, adCmdText

instead of

Set rs = conn.Execute(Sql)

Thanks guys
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Feel free to accept your post as the solution.
Mike MillerSoftware Engineer

Author

Commented:
Figured it out myself