Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordcount (access 2000)

    for the following program:

    Dim strSQL As String
    Dim dbs As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim bytX As Byte
    Dim bytY As Byte
    strSQL = "select * from transactions where invoiceid = '" & strIID & "'"
    Set dbs = CurrentProject.Connection
    rst.Open strSQL, dbs, adOpenDynamic, adLockOptimistic
    msgbox rst.recordcount

    I wonder whey the recordcount is -1 while in debug I can see there are three records there in the selected recordset.

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    RecordCount only counts the records that you have accessed. So far you have not looked at any!
    You need to use the Move Last method to access all the records before counting.

    From the online help

    "When you work with linked TableDef objects, the RecordCount property setting is always

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

    Re: recordcount (access 2000)

    That's true, but ADO doesn't have TableDef objects.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    I only ever got as far as A97 LOL. Company is to tight to upgrade!

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

    Re: recordcount (access 2000)

    If you just call the Recordcount property in ADO, it returns a -1 if there are records and a 0 if there are none, a bit more informative that DAO which returns a zero regardless until you move to the end of the recordset. You won't get the actual count in either ADO or DAO until you do a MoveLast on the recordset.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    I did try to use the rst.movelast but the value is still -1. The way (which is rather stupid) that I get around this problem is

    do while rst.eof <> true
    bytX = bytX + 1
    rst.movenext
    loop
    msgbox bytX

    Even I have looped through all the records in the recordset, the rst.recordcount is still -1

    I have attached the database file with the two different way to count the records (only 3 rec) and hopefully you may be able to see the problem.

    Thanks very much
    Attached Files Attached Files

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

    Re: recordcount (access 2000)

    Try using adOpenStatic instead of adOpenDynamic and you'll get a count of 3 for your recordcount after the MoveLast.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    Thanks it works but adOpenStatic does not allow me to delete, modify the records. I hope there is a way to get around this problem.

    Thanks very much.

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

    Re: recordcount (access 2000)

    You need the OpenStatic to get the recordcount. You didn't say anything at all about using the recordset any other way. You can get the count or you can edit the recordset, but not both with the same recordset opened the same way.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    Yes, I should have said so way in the beginning. Sorry for that. Well I guess the world is not perfect, not in Access anyway.

    Thanks for clearing this up. Your help is deeply appreciated.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: recordcount (access 2000)

    Hi,
    It's one of the joys of ADO - if you look at Recordcount in Access 2000 help, you'll find this:
    "The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor, the actual count for a static or keyset cursor, and either -1 or the actual count, depending on the data source, for a dynamic cursor."
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    I notice that if I import the form and the database table into SQL server 7, the recordcount will give me the right number of records even if I use adOpenDynamic. The same program in Access will give me -1.

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

    Re: recordcount (access 2000)

    I don't know what you mean by the "same program in Access". Are you are using ADO in a stored procedure in SQL Server, or are you talking about using Access VBA code with ADO against a SQL Server table?
    Charlotte

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    I had the same problem trying to access a SQL Server table with ADO until I realized that the Cursor location was the trick for me... Try setting the Cursor Location of the Recordset to adUseClient:

    '...
    rst.CursorLocation = adUseClient
    '...

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  15. #15
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (access 2000)

    what I meant is the same following codes will give you different recordcount values when run it under mdb on Access database and under adp on sql server database:

    rst.open "city" dbs, adOpenDynamic, adLockOptimistic
    rst.movelast
    msgbox rst.recordcount

    If you run my previously attached mdb file, you get -1 just like what you said before. If you create a database in SQL server 7 and import my city table into SQL server as a table. Create an Access project adp and then import my form (from the mdb). If you run that same form but now apply it against the SQL server table (which is identical to the mdb city table). You will notice that the result is now correct even though you are using adOpenDynamic. It returns the right number of records as you use adOpenStatic.
    This same program while run under mdb will give you -1 because adOpenDynamic and the right number if you use adOpenStatic.

    Hope this clear the cloud.

Posting Permissions

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