VBA/Excel/Access/Word/Access/Recordset Count

Материал из VB Эксперт
Перейти к: навигация, поиск

Counting the Number of Returned Records

   <source lang="vb">

Sub CountRecords()

  Dim conn As ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  Dim myarray As Variant
  Dim returnedRows As Integer
  Dim r As Integer "record counter
  Dim f As Integer "field counter
  Set conn = CurrentProject.Connection
  Set myRecordset = New ADODB.Recordset
  myRecordset.Open "SELECT * FROM Employees", conn, adOpenForwardOnly, adLockReadOnly, adCmdText
  myarray = myRecordset.GetRows()
  returnedRows = UBound(myarray, 2) + 1
  For r = 0 To UBound(myarray, 2)
     Debug.Print "Record " & r + 1
     For f = 0 To UBound(myarray, 1)
        Debug.Print "  "; myRecordset.Fields(f).Name & " = " & myarray(f, r)
     Next f
  Next r
  myRecordset.Close
  Set myRecordset = Nothing
  conn.Close
  Set conn = Nothing

End Sub

</source>
   
  


Get the Number of Returned Records

   <source lang="vb">

Sub GetCount()

  Dim conn As ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  Dim myarray As Variant
  Dim returnedRows As Integer
  Dim r As Integer "record counter
  Dim f As Integer "field counter
  Set conn = CurrentProject.Connection
  Set myRecordset = New ADODB.Recordset
  myRecordset.Open "SELECT * FROM Employees", _
     conn, adOpenForwardOnly, adLockReadOnly, adCmdText
  myarray = myRecordset.GetRows()
  returnedRows = UBound(myarray, 2) + 1
  MsgBox "Total number of records: " & returnedRows
  myRecordset.Close
  Set myRecordset = Nothing
  conn.Close
  Set conn = Nothing

End Sub

</source>