By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

ADODB & Queries

P: n/a
On a form, I have a command button that has the following event onClick:

Private Sub Command0_Click()
Dim rslt As ADODB.Recordset, sqlstr As String
sqlstr = "SELECT Table1.id, Table1.name, Table1.value FROM Table1
WHERE Table1.name like '%" & Text1.Value & "%'"
Set rslt = New ADODB.Recordset
Set rslt.ActiveConnection = CurrentProject.Connection
rslt.Open sqlstr
MsgBox rslt.RecordCount
End Sub

No matter what I enter into the Text1 box (on the form), I always get a
RecordCount of -1.

So, I created a query by selecting SQL view and pasting in:

SELECT Table1.id, Table1.name, Table1.value FROM Table1 WHERE
Table1.name like '%jo%'

When I run the query, I get 6 rows returned.

What (likely obvious) detail am I missing here? I threw in a MsgBox
sqlstr to be sure that the query looks exactly as it should...

--
Justin Koivisto - ju****@koivi.com
http://koivi.com
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
By default the cursor type of an ADO recordset is adOpenForwardOnly.
This will not allow the return of RecordCount. Set the cursor type to
AdOpenKeySet or AdOpenStatic and RecordCount should be accurate.
You may also want/need to consider CursorLocation and LockType,
depending on what you want to do.

Nov 13 '05 #2

P: n/a
ly******@yahoo.ca wrote:
By default the cursor type of an ADO recordset is adOpenForwardOnly.
This will not allow the return of RecordCount. Set the cursor type to
AdOpenKeySet or AdOpenStatic and RecordCount should be accurate.
You may also want/need to consider CursorLocation and LockType,
depending on what you want to do.


Lyle, what is the ADO openrecordsettype equivalent of the DAO
dbOpenSnapshot?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #3

P: n/a
For an old article which discusses this see:
http://msdn.microsoft.com/library/de...ml/ima0501.asp

Nov 13 '05 #4

P: n/a
ly******@yahoo.ca wrote:
For an old article which discusses this see:
http://msdn.microsoft.com/library/de...ml/ima0501.asp


Thanks, appreciate it, that will help me.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5

P: n/a
ly******@yahoo.ca wrote:
By default the cursor type of an ADO recordset is adOpenForwardOnly.
This will not allow the return of RecordCount. Set the cursor type to
AdOpenKeySet or AdOpenStatic and RecordCount should be accurate.
You may also want/need to consider CursorLocation and LockType,
depending on what you want to do.


Thanks, that is exactly what I needed.

--
Justin Koivisto - ju****@koivi.com
http://koivi.com
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.