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

<<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.>>

  It's hit or miss depending on the size of the recordset.  If JET thinks
it's going to take too long to fully populate it, it finishes the job in the
background.  Code starts executing again while this is going on, so
.Recordcount will be incorrect when this happens.

Jim Dettman
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
Sent: Thursday, October 14, 2004 3:34 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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
>
>

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com