CODEWITHSUNDEEP
ms-accessvba
user3228341
user3228341

Reputation: 11

DAO method in MS Access

I am unable to get the count of records by openining Ms Access Query, I use the following code.

Private Sub CmdGetData_Click()
Dim WRK As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSql As String

Set WRK = DBEngine.Workspaces(0)
Set db = CurrentDb
StrSql = "select * from [QrySalePatti]"

Set rs = db.OpenRecordset(StrSql, dbOpenDynaset)

Do While (Not rs.EOF)
  rs.MoveFirst
  rs.MoveLast
  MsgBox rs.RecordCount
Loop

exitRoutine:
If Not (db Is Nothing) Then
     db.Close
     Set db = Nothing
End If

Set WRK = Nothing
End Sub

Upvotes: 1

Views: 107

Answers (1)

HansUp
HansUp

Reputation: 97101

You should not need a Do While loop to get the RecordCount.

Set rs = db.OpenRecordset(StrSql, dbOpenDynaset)
With rs
    If Not (.BOF And .EOF) Then
        .MoveLast
    End If
    MsgBox .RecordCount
End With

However if your goal is only to count the rows from QrySalePatti, you could use a SELECT Count(*) query and read the value returned from that.

StrSql = "SELECT Count(*) AS row_count FROM [QrySalePatti]"
Set rs = db.OpenRecordset(StrSql)
MsgBox rs!row_count

Or you could use a DCount expression.

MsgBox DCount("*", "QrySalePatti")

Upvotes: 1

Related Questions