ADO - Record count always -1 (AccessXP)

[Marie-Therese]

I am trying to return the recordcount from a query using ADO. The query is run with ADO using the connection object and the syntax is ..

"SELECT distinct tblTable.txtField FROM qryQuery ORDER BY tblTable.txtField".

Why is it that when I run this query, the recordcount is -1? In fact, there are many records returned.

Also, I have added a simple WHERE clause to the query:

"SELECT distinct tblTable.txtField FROM qryQuery WHERE tblTable.txtField like "*value*" ORDER BY tblTable.txtField",

The result of the above is always no records. However, if I run this query directly in an Access query (instead of using ADO), I do get the results I have asked for.

Is there anything I am doing here that is strange?

Thanks!

[charlotte]

What CursorType are you using? If you use a forward only cursor type, you'll always get a -1 instead of the actual record count when there are records returned. Without seeing the code you use to open the recordset, that would be my guess at your problem.

[Marie-Therese]

Charlotte,

Many thanks. I changed it to the Static type recordset and the recordcount now returns the number of records.

However, I still had lots of trouble executing the WHERE clause. It always returns 0 records. So, someone here helped me and by changing the syntax to
"like %value%"
it worked.


For some reason it needs ANSI SQL, instead of Access SQL. I am executing this command through VBA.

[charlotte]

If you use ADO, you have to be careful of the SQL. It uses the ANSI wildcards like SQL Server rather than what you might have been used to with DAO and earlier versions of Jet.

[jeremybarker]

You should also be aware that if you define a query through the Access user interface using wildcards like * and ? in like clauses, then the query will run correctly when run through the user interface but may return different (or no records) if opened in an ADO recordset!

This has caught me out on a couple of occasions during the last 6 months.

Regards

Jeremy