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?
Mike MillerSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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
0
NorieVBA ExpertCommented:
Try adding this just before the if statement.
rs.MoveLast
rs.MoveFirst

Open in new window

0
Wasim Akram ShaikCommented:
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/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike MillerSoftware EngineerAuthor 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.
0
Mike MillerSoftware EngineerAuthor Commented:
Got it...

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

instead of

Set rs = conn.Execute(Sql)

Thanks guys
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Feel free to accept your post as the solution.
0
Mike MillerSoftware EngineerAuthor Commented:
Figured it out myself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.