DAO and Recordcount Max records

  • Thread starter Thread starter tommylux
  • Start date Start date
T

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
Click to expand...
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
 
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
---------------------------
 
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.
 
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?
 
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
 
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.
 
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
 
Tried MsgBox db.TableDefs("Staff").RecordCount

It says "Item not found in this collection"
Click to expand...

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

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

AFAIK, it worked fine all the way back in Access 97.
 
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
---------------------------
 
Back
Top