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

Jim Dettman jimdettman at earthlink.net
Thu Oct 14 15:27:46 CDT 2004


Sure that would work as your explicitly forcing JET to count all the
records.  But it would be faster if it was:

mysql = "SELECT"
mysql = mysql & " Count(*) AS RecordCount"
mysql = mysql & " FROM"
mysql = mysql & " CONTACTS;"

  JET will just get the count from the primary key index if there is one.

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
jeffrey.demulling at usbank.com
Sent: Thursday, October 14, 2004 4:06 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Help with syntax please....OpenRecordset


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

Why not use something along the lines of?

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mysql as string

mysql = "SELECT"
mysql = mysql & " Count(CONTACTS.CONTACTKEY) AS RecordCount"
mysql = mysql & " FROM"
mysql = mysql & " CONTACTS;"

Set db = CurrentDb
Set rst = db.OpenRecordset(mysql)
MsgBox rst.Fields("RecordCount")


----------------------------------------------------------------------------
--
Electronic Privacy Notice. This e-mail, and any attachments, contains
information that is, or may be, covered by electronic communications privacy
laws, and is also confidential and proprietary in nature. If you are not the
intended recipient, please be advised that you are legally prohibited from
retaining, using, copying, distributing, or otherwise disclosing this
information in any manner. Instead, please reply to the sender that you have
received this communication in error, and then immediately delete it. Thank
you in advance for your cooperation.
============================================================================
==

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





More information about the AccessD mailing list