[AccessD] Help with syntax please....OpenRecordset

Are you sure Susan? Here's my findings with a table of 6 records

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("CONTACTS", dbOpenTable)
MsgBox rst.RecordCount

Gives an answer of 6

But

Set rst = db.OpenRecordset("SELECT * FROM CONTACTS")
MsgBox rst.RecordCount

Gives an answer of 1.

As does
Set rst = db.OpenRecordset("CONTACTS", dbOpenDynaset)

If you then do

Set rst = db.OpenRecordset("SELECT * FROM CONTACTS")
If Not rst.EOF Then
  rst.MoveLast
  rst.MoveFirst
  MsgBox rst.RecordCount
End If

You get a reliable 6. (Note to folks, donn't do a Movelast without checking
EOF. If the table's empty the Movelast collapses). So I reckon Dynaset
recordtypes support RecordCount but do require the Movelast.

-- Andy Lacey
http://www.minstersystems.co.uk 


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Susan Harkins
> Sent: 14 October 2004 17:19
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Help with syntax please....OpenRecordset
> 
> 
> Some recordsets support the RecordCount property, some don't. 
> Those that do don't require the move through the records to 
> get a count. Just refer to the RecordCount property. Just 
> wrote an article on it for Inside Microsoft Access -- should 
> be out this winter sometime. 
> 
> Susan H. 
> 
> Mmmzzz don't wanna be fuzzy but one remark about the 
> codesample though just in case others wanna use it... To use 
> a recordcount if I remember well, you should always do a 
> rs.movelast and a rs.movefirst first to populate the 
> recordset and get a reliable recordcount.
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>