Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO - Record count always -1 (AccessXP)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO - Record count always -1 (AccessXP)

    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.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO - Record count always -1 (AccessXP)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO - Record count always -1 (AccessXP)

    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.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO - Record count always -1 (AccessXP)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •