ADODB & Queries

[Justin Koivisto]

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

[lylefair]

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.

[Tim Marshall]

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

[lylefair]

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

[Tim Marshall]

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

[Justin Koivisto]

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