Member_2_1242703
asked on
VBA ADODB Getting a record count using RecordSet
The following code connects and runs with no errors, but not getting the expected result
The value of rs.RecordCount is -1 but there are 11 records in the table. What am I doing wrong here?
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 IfThe value of rs.RecordCount is -1 but there are 11 records in the table. What am I doing wrong here?
Try adding this just before the if statement.
rs.MoveLast
rs.MoveFirst
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/
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/
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Feel free to accept your post as the solution.
ASKER
Figured it out myself
rs.CursorLocation = adUseClient
From many sources. For example:
http://www.adopenstatic.com/faq/recordcounterror.asp