Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    48

    Unanswered: Cannot count records in recordset

    In the subroutine below, I am trying to find the number of records in the Query qryIDs.

    The results below give a count of -1, whereas if I run the Query outside of VBA, it correctly shows 28 records.

    I would appreciate any suggestions and pointers as to where I am going wrong


    sub ThrowDataToExcel()

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    dim varRecCount as Long

    strSQL1 = "SELECT * "
    strSQL1 = strSQL1 + "FROM qryIDs"

    rst.Open strSQL1, ThisDB, adOpenDynamic, adLockReadOnly

    varRecCount = rst.RecordCount <<< Error: value of minus 1

    Exit
    Last edited by JillB; 01-15-08 at 04:40.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It depends on the cursor location and a few other recordset properties. A recordset is a cursor - it cycles through result sets one row at a time. By default the cursor location is at the server so the recordset only requests rows as and when you access them. -1 means it has records. If that is all you want to know ("does this contain records or not?") stick with that. If you must now how many records are returned then you need to fully populate the recordset - try moving to the last record - this retrieves all the intermediate ones too.

    EDIT - note that this is inefficient if you don't intend to subsequently use all the rows - in that case you should create a query starting SELECT COUNT(*)....

    Actually - just seen the name of your proc. Have you seen my export to excel code in the codebank?

    HTH
    Last edited by pootle flump; 01-15-08 at 04:47.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't remember now where I read it, but I don't beleive a Dynamic recordset supports the RecordCount property.

    Whenever I need the RecordCount I always use adOpenKeyset. And like Pootle Flump said, you will need to move the end the recordset to get an accurate count, then move back to begin a record by record loop.

  4. #4
    Join Date
    Dec 2007
    Posts
    48
    Quote Originally Posted by DCKunkle
    you will need to move the end the recordset to get an accurate count, then move back to begin a record by record loop.
    thanks for this valuable information, much appreciated.

    Could I ask if you could give me a couple of lines of some code showing how to get this count in the way you suggest? I am new to Access, and finding the going a bit tough

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    sub ThrowDataToExcel()

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    dim varRecCount as Long

    strSQL1 = "SELECT * "
    strSQL1 = strSQL1 + "FROM qryIDs"

    rst.Open strSQL1, ThisDB, adOpenDynamic, adLockReadOnly
    or
    rst.Open strSQL1, ThisDB, adOpenKeyset, adLockReadOnly <- If not updating data, I usually use this.
    or
    rst.Open strSQL1, ThisDB, adOpenDynamic, adLockOptimistic <- If updating, I usually use this.

    rst.movelast <- try adding this line here.
    rst.movefirst <- you probably don't need this line.

    varRecCount = rst.RecordCount
    Last edited by pkstormy; 01-15-08 at 18:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2007
    Posts
    48
    Excellent, works like a charm!! Many thanks to each of you, and to Paul for taking the time to document the steps in detail. I have learnt lots from this. So easy when you know how, eh?

    kind regards,
    Jill

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Remember that if ALL you want is a count of the number of records, rather than a count PLUS all the data, then you should submit a COUNT query instead. If you wanted to know how many people are in your extended family you wouldn't invite them round to your house just to count them

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd just invite my gran, she'd be able to tell me all of them!
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2007
    Posts
    48
    Lol

Posting Permissions

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