[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA ADODB Getting a record count using RecordSet

Posted on 2014-08-05
7
Medium Priority
?
1,006 Views
Last Modified: 2014-08-17
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?
0
Comment
Question by:Mike Miller
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40242697
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
 
LVL 35

Expert Comment

by:Norie
ID: 40242700
Try adding this just before the if statement.
rs.MoveLast
rs.MoveFirst

Open in new window

0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40242920
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Mike Miller
ID: 40242934
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
 

Accepted Solution

by:
Mike Miller earned 0 total points
ID: 40242959
Got it...

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

instead of

Set rs = conn.Execute(Sql)

Thanks guys
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40243435
Feel free to accept your post as the solution.
0
 

Author Closing Comment

by:Mike Miller
ID: 40265933
Figured it out myself
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question