Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: Recordset returning a record count of -1 (even though there are rows in the table)

    I am having issues with getting a valid recordcount back from a table.
    Through ADO, I have a VB app hooked up to an Access 2000 database.
    I have a table called "Inv" which has over 20000 rows.
    I set up the connection (no problems there) and I create the recordset (again no problems)

    when I trace through the code, I don't get any errors, but when I do a recordcount, it states that the recordcount is -1.
    Why is this?

    See the code below

    Private Sub ConnectToDatabase()

    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim strSQL As String

    On Error GoTo err_handler

    Set adoConn = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
    Set adoCmd = New ADODB.Command


    strDBPath = "c:\Nels\db1.mdb"


    With adoConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open strDBPath
    End With


    adoRS.Open "Inv", adoConn


    'display the number of records in the recordset
    MsgBox "Record Count = " + adoRS.RecordCount

    Set adoConn = Nothing
    Set adoRS = Nothing
    Set adoCmd = Nothing

    Exit Sub


    err_handler:
    MsgBox Err.Description

    Set adoConn = Nothing
    Set adoRS = Nothing
    Set adoCmd = Nothing
    End Sub


    Thanks
    Conor

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Recordset returning a record count of -1 (even though there are rows in the table)

    Originally posted by Kilvoultra
    I am having issues with getting a valid recordcount back from a table.
    Through ADO, I have a VB app hooked up to an Access 2000 database.
    I have a table called "Inv" which has over 20000 rows.
    I set up the connection (no problems there) and I create the recordset (again no problems)

    when I trace through the code, I don't get any errors, but when I do a recordcount, it states that the recordcount is -1.
    Why is this?

    See the code below

    Private Sub ConnectToDatabase()

    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim strSQL As String

    On Error GoTo err_handler

    Set adoConn = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
    Set adoCmd = New ADODB.Command


    strDBPath = "c:\Nels\db1.mdb"


    With adoConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open strDBPath
    End With


    adoRS.Open "Inv", adoConn


    'display the number of records in the recordset
    MsgBox "Record Count = " + adoRS.RecordCount

    Set adoConn = Nothing
    Set adoRS = Nothing
    Set adoCmd = Nothing

    Exit Sub


    err_handler:
    MsgBox Err.Description

    Set adoConn = Nothing
    Set adoRS = Nothing
    Set adoCmd = Nothing
    End Sub


    Thanks
    Conor
    Set the CursorType argument to adOpenKeyset or adOpenStatic to get the recordcount. The default if non is specified is adOpenForwardOnly.

    From the help files:

    RecordCount Property


    Indicates the number of records in a Recordset object.

    Return Value

    Returns a Long value that indicates the number of records in the Recordset.

    Remarks

    Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

    If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset, regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

    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 for a dynamic cursor, depending on the data source.

    Hope this helps.

    Gregg

Posting Permissions

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