DAO and Recordcount Max records

[tommylux]

I am a little new on Google Groups but decided to use it as I have
looked at it so many times.

From research, I have found out that a "Staff.MoveLast" must be placed

at 'X for it to give me the correct amount.

Dim db As DAO.Database
Dim NewStaff As DAO.Recordset
Set db = CurrentDb()
Set NewStaff = db.OpenRecordset("Staff") 'This is a query
'X
MsgBox NewStaff.RecordCount

NewStaff.Close

Should this code be based on a table, It would seem that Staff.MoveLast
is not needed. Is this correct?

As it is based on a query, is recordcount the current record or is it
the total? If it is the total, could I use Movelast then Move first
before running recordcount?

Is there a better way to count the number of records in a table?

Tom

 

[Graham R Seach]

Tommy,

If you want to reliably count records in a table or recordset, you should
first issue rs.MoveLast:
Set NewStaff = db.OpenRecordset("Staff")
NewStaff.MoveLast
MsgBox NewStaff.RecordCount
NewStaff.MoveFirst

I haven't tested the following, so I don't know how reliable it is, but:
MsgBox db.TableDefs("Staff").RecordCount

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

 

[tommylux]

Tried MsgBox db.TableDefs("Staff").RecordCount

It says "Item not found in this collection"

If do:

Dim db As DAO.Database
Dim NewStaff As DAO.Recordset
Set db = CurrentDb()
Set NewStaff = db.OpenRecordset("Staff") 'This is a query
Staff.MoveLast

'Moving to first record:
Staff.MoveFirst

'and maybe a loop with:
Staff.MoveNext

'Does:
MsgBox NewStaff.RecordCount
'Remain the same value or is it the record reference?

NewStaff.Close


Else, I could try simply doing:

Dim RSCount as Integer
Staff.MoveFirst
Staff.MoveLast
RSCount = Staff.RecordCount

and using the RSCount for what I need to use it for.

 

[Tommylux]

I have another problem,

This query can return no records (identifying no new staff)

so MoveFirst and MoveLast will cause the VB to crash.

Any Ideas?

 

[Graham R Seach]

Tommy,

I'm not sure when the TableDefs object's RecordCount property was
introduced. You must be using something prior to Access 2003.

Before you go trying to move about in a recordset, you should check that it
as some records:

Set NewStaff = db.OpenRecordset("Staff")
If NewStaff.AbsolutePosition > -1 Then
NewStaff.MoveLast
RSCount = Staff.RecordCount
NewStaff.MoveFirst

Do While Not NewStaff.EOF
'Looping code
NewStaff.MoveNext
Loop
End If

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Tommylux]

Yes, Using Access 2000 despite we are the MI administrators, IT get
2003.

Thanks for helping me out, the other side of the world!

Bye for now.

 

[Dirk Goldgar]

I am a little new on Google Groups but decided to use it as I have
looked at it so many times.

at 'X for it to give me the correct amount.

Dim db As DAO.Database
Dim NewStaff As DAO.Recordset
Set db = CurrentDb()
Set NewStaff = db.OpenRecordset("Staff") 'This is a query
'X
MsgBox NewStaff.RecordCount

NewStaff.Close

Should this code be based on a table, It would seem that
Staff.MoveLast is not needed. Is this correct?

If you open a recordset directly on a local -- not linked -- table, then
by default you get a table-type recordset. In that case you don't need
the call to .MoveLast. If you open your recordset on a query, or on a
linked table, then you can't get a table-type recordset. By default, it
will be a dynaset-type recordset. For any other type of recordset than
table-type, the .RecordCount property will not be accurate until all
records in the recordset have been visited. Calling the .MoveLast
method makes that happen; that's why you need it in that case.

As it is based on a query, is recordcount the current record or is it
the total? If it is the total, could I use Movelast then Move first
before running recordcount?

Yes, but you have to make sure first that you have at least one record
in the recordset:

Dim db As DAO.Database
Dim NewStaff As DAO.Recordset

Set db = CurrentDb()
Set NewStaff = db.OpenRecordset("Staff")
With NewStaff
If Not .EOF Then
.MoveLast
.MoveFirst
End If
MsgBox .RecordCount
.Close
End With

Is there a better way to count the number of records in a table?

If you're willing to assume that it's always going to be a local table,
you can get it from the TableDef:

MsgBox CurrentDb.TableDefs("YourTableName").RecordCount

I don't like that, though, because it precludes the possibility that you
may split the database and use linked tables.

Simpler, quicker, and in general more efficient than opening and
traversing a full recordset:

Dim StaffCount As DAO.Recordset

Set StaffCount = _
DBEngine.WorkSpaces(0)(0).OpenRecordset( _
"SELECT Count(*) As NRecs FROM Staff")
With StaffCount
MsgBox .Fields(0)
.Close
End With

Or, of course, you could use the DCount function.

 

[Dirk Goldgar]

Tried MsgBox db.TableDefs("Staff").RecordCount

It says "Item not found in this collection"

If "Staff" is a query, as you originally said, then it won't be in the
TableDefs collection.

 

[Dirk Goldgar]

Tommy,

I'm not sure when the TableDefs object's RecordCount property was
introduced. You must be using something prior to Access 2003.

AFAIK, it worked fine all the way back in Access 97.

 

[Graham R Seach]

Thanks for the info Dirk. I only had 2003 with me, so I couldn't check it.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

 

[Tommylux]

Thanks for all your help guys.

Works great.