ADO RecordCount differs from DAO RecordCount

[sterlecki]

I tried two diffent ways of opening a recordset using the same strSQL and got two different answers.

This routine captures the Name of a subform and it's recordsource (see below) for use in this onclick event.

The highlighted debug.print rs.recordcount returns different answers. If I take out the DISTINCT word then I get a -1 (ADO) and 1 (DAO)

Private Sub cmd_UseCasingDefaults_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld3 As DAO.Field
    Dim rst1 As ADODB.Recordset 'The default table
    Dim fld1 As ADODB.Field
    Dim rst2 As ADODB.Recordset  'The data_??? table
    Dim fld2 As ADODB.Field
    Dim frmName As String
    Dim frmRecSource As String
    Dim strSQL As String
    Dim strAPI As String
    Dim strOperationsGroup As String
    
    
'        MsgBox getSubFrmName()
'        MsgBox getSubFrmRecordsource
        'Capture API number of well
        strAPI = Me.cbo_SelectWell
        
'        'Capture recordsource name from subform of interest
        frmRecSource = getSubFrmRecordsource()
        
        Debug.Print frmRecSource
        
'
        'set SQL statement


        strSQL = "Select DISTINCT * FROM (default_Pinedale)   WHERE default_Pinedale.TableName =  '" & frmRecSource & "' "
       Debug.Print strSQL
    
    'Use ADO recordset
    
    Set rst1 = New ADODB.Recordset
    Set rst1 = CurrentProject.Connection.Execute(strSQL)
    Debug.Print rst1.RecordCount 
	[highlight]'returns a count of -1 [/highlight]
    
    'Use DAO recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    Debug.Print rs.RecordCount  
[highlight]'returns a count of 3 and is correct[/highlight]
    
    
    'List fields for ADO recordset

    For Each fld1 In rst1.Fields
        Debug.Print fld1.name
    Next fld1
    
    rst1.Close
    Set rst1 = Nothing
    
    For Each fld3 In rs.Fields
        Debug.Print fld3.name
    Next fld3
    
    rs.Close
    Set rs = Nothing
 

 
End Sub

Three Functions go with this: (credits to MajP and PHV for their help)

'captures the name of the subform on the current tabbed page

Public Function getSubFrm() As Access.Form
  Dim pg As Access.Page
  Dim ctl As Access.Control
  Set pg = Me.TabCtl60.Pages(TabCtl60.Value)
  For Each ctl In pg.Controls
    If ctl.ControlType = acSubform Then
      Set getSubFrm = ctl.Form
      Exit Function
    End If
  Next ctl
End Function


'Retrieves the Subform name

Public Function getSubFrmName()
  getSubFrmName = getSubFrm.name
End Function

'Retrieves the Subform Recordsource

Public Function getSubFrmRecordsource()
  getSubFrmRecordsource = getSubFrm.RecordSource
End Function

 

[dhookom]

You can't expect to get the correct record count unless you move to the end of the recordset.

Duane
Hook'D on Access
MS Access MVP

 

[sterlecki]

Not exactly sure what you mean. DAO recordset returns 3 which is the correct answer without moving to the end.

Anyway I placed this:

rst1.movelast just before the debug.print stmnt and it returned a -1 as a record count.

Another thought: in the rst1.open stmnt the last parameter says "Options as Long = -1" Not sure what this does but the -1 is suspicious.

thoughts?

 

[dhookom]

I had thought if there were a higher number of records, you would need to movelast to get the correct record count. I haven't and probably won't test this supposition.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

ADO recordsets usually need to be fully populated before RecordCount is accurate (hence the need to do a MoveLast).

I never use RecordCount, I just use an SQL statement with Count instead - that's almost guaranteed to be more efficient than opening a recordset, moving to last record, and checking RecordCount.

 

[PHV]

Why using the Execute method instead of Open ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Thanks JoeAtWork for that insight. I'm just using the recordcount as I test this code.

PHV
I tried the open method with the same result.

Without the DISTINCT predicate
ADO returns -1
DAO returns 1

With the DISTINCT or with ALL predicate
ADO -1
DAO 3 (correct Answer)

I don't understand why I would need these predicates to return 3 records. They are all unique rows. Is something wrong with the SQL?

Set rst1 = New ADODB.Recordset
    'Set rst1 = CurrentProject.Connection.Execute(strSQL)
    Set rst1.ActiveConnection = CurrentProject.Connection
    rst1.Open (strSQL)
    rst1.MoveLast
    Debug.Print rst1.RecordCount

this project is in Access 2007 saved as .accdb