Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: [2007] ADO Recordset Record Count

    I simply trying to get the count of records in a recordset. This should easy but I keep getting a record count of -1. I know that the recordset contains records because I'm indexing through the RS and accessing all records.

    I found a site that recommends doing this

    Code:
    When using ADO, use a static or keyset cursor to return the actual record count
    What is a static or keyset cursor?

    Code:
     
    Dim rsdo As ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim cmdDo As ADODB.Command
     
    Set rsdo = New ADODB.Recordset
    Set cmdDo = New ADODB.Command
     
    With cmdDo
            .ActiveConnection = CurrentProject.Connection
            .CommandType = adCmdStoredProc
            .CommandText = "TMI_DO_Mapping"
            Set rsdo = .Execute()
    End With
     
    rsdo.Filter = "Node = " & idx
     
    rsdo.MoveFirst
     
    Print #fMap.outputnumber, "DO Count = " & rsdo.RecordCount

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I know when I need a record count, I have to nagivate to the last record before doing it.

    rs.MoveLast
    MsgBox rs.RecordCount
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To do recordset counts in ADO (note rs = rsdo in your code)

    strSQL = "Select * from ....."

    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    X = rs.recordcount

    (this recordset will be non-updateable though!!)

    To edit the recordset (but you will get -1 for the rs.recordcount)...

    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic

    (note: you can try this but I think you'll still get -1)
    rs.movelast
    rs.movefirst
    X = rs.recordcount



    Another way to write your code (which I usually find much easier than what you have, providing it's connecting to a linked or MSAccess table - ie. no need to dim a conn as adodb.connection variable)...

    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from ...."
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic (you'll notice the autoexpand help when you type this out.)

    but you may need to dim a connection variable if you're directly connecting to a view or stored procedure in SQL Server. Unnecessary if the table is linked or an MSAccess table.
    Last edited by pkstormy; 06-18-09 at 21:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2005
    Posts
    333
    Thanks guys.

    I tried doing the movelast, movefirst before my original post ans it does not work.

    PK, I usually define my queries as you have but I got lazy and am trying to use an Access defined query. The SQL for this is shown below. Is there another way to use the Access defined query?

    My work around for now is to iterate through my recordset once and do nothing but increment a counter (toget the record count) then movefirst and start my code. Very inefficient.

    Maybe my laziness is making more work than it is saving.


    HTML Code:
    SELECT map.Position, map.Node, map.Chassis, map.Slot, ChanBitX.Channel, ChanBitX.NewBit, Iodolo.DOvar, Iodolo.DOdim, Iodolo.DOsdl, Iodolo.DOsc, Iodolo.DOword, Iodolo.DOchan
    FROM (Iodolo INNER JOIN map ON (Iodolo.DOword = map.old_Slot) AND (Iodolo.DOsc = map.RPC) AND (Iodolo.DOsdl = map.SDL)) INNER JOIN ChanBitX ON Iodolo.DOchan = ChanBitX.OldBit
    ORDER BY Iodolo.DOsdl, Iodolo.DOsc, Iodolo.DOword, Iodolo.DOchan;

Posting Permissions

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