Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADODB.Recordset Recordcount (2000)

    Hi

    I am trying to find out how many records I have in a ADODB.Recordset. The code I have returns the correct recordset, I just want to know how many records I have. I am trying to use
    Dim mobjVC_ClientList As New ADODB.Recordset
    mobjVC_ClientList.ActiveConnection = mcnn1
    mobjVC_ClientList.Open "select * from VC_ClientList where ni_number is not null"
    debug.print mobjVC_ClientList..recordcount

    However I always get a record count of -1

    Any help as always is much appreciated.

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

    Re: ADODB.Recordset Recordcount (2000)

    Recordcount behaves differently than you might expect. The -1 means that there are records in the recordset but you will need to add a call to
    <code>mobjVC_ClientList.MoveLast </code>
    before you can get the actual count..
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADODB.Recordset Recordcount (2000)

    Try inserting this line before the line that opens the recordset:

    mobjVC_ClientList.CursorType = adOpenKeyset

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADODB.Recordset Recordcount (2000)

    If you don't specify the cursor type, ADO opens a recordset of type adOpenForwardOnly. This cursor type doesn't support the MoveLast method. RecordCount will always result in -1 for a forward-only recordset.
    If you specify adOpenKeySet, RecordCount will return the actual number of records even without using MoveLast.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADODB.Recordset Recordcount (2000)

    Thanks Hans. Works a treat

Posting Permissions

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