getting current record count in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 01 Mar 2007 15:12 GMT

I have a form that is based off of a query. the query gathers its
information from a single table. i have a seperate (custome MFC) program
that adds records to the table.

is there a way to get a current count of records in the table?

i'm using

Dim dbsC1894 As Database
Dim intRecordCount As Integer

Set dbsC1894 = CurrentDb

intRecordCount = dbsC1894.TableDefs![tblCoils].RecordCount

RecordCount = intRecordCount

where tblCoils in the data table, and RecordCount is a value box on the form
page

what would someone suggest?

Reply to this Message

Michel Walsh - 01 Mar 2007 15:50 GMT

Since filling a recordset is asynch, the RecordCount can be wrong, unless
you force the recordset to move to its end. If you don't really want to do
that, you can simply ask a COUNT(*):

intRecordCount = DCount("*", "tableName")

or issue an explicit SQL statement that would do the same.

Hoping it may help,
Vanderghast, Access MVP

>I have a form that is based off of a query. the query gathers its
> information from a single table. i have a seperate (custome MFC) program
[quoted text clipped - 18 lines]
>
> what would someone suggest?

Reply to this Message

Mike - 01 Mar 2007 16:17 GMT

I had some discussion about the DCount function, and I was told that it is a
rather "slow" calculation and could alter the DB performance.

I believe the programmer said for his MFC data entry program he can add a
"move first, move last" command at the end of his data entry program. That
should give the "correct" number of records?

also what do you mean by "explicity SQL statement" ??

> Since filling a recordset is asynch, the RecordCount can be wrong, unless
> you force the recordset to move to its end. If you don't really want to do
[quoted text clipped - 29 lines]
> >
> > what would someone suggest?

Reply to this Message

Douglas J. Steele - 01 Mar 2007 16:41 GMT

Dim rsCurr As DAO.Recordset

Set rsCurr = CurrentDb().OpenRecordset( _
"SELECT Count(*) As TotalRecords FROM tblCoils" & _
)
MsgBox "There are " & rsCurr!TotalRecords & " records in tblCoils"

rsCurr.Close
Set rsCurr = Nothing

Realistically, I doubt you'll see much impact using DCount though.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I had some discussion about the DCount function, and I was told that it is
>a
[quoted text clipped - 42 lines]
>> >
>> > what would someone suggest?

Reply to this Message

Michel Walsh - 01 Mar 2007 19:53 GMT

Indeed, the movelast will take much the same time, if not more, since
filling the recordset has probably more overhead than getting a scalar
value.

An explicitly built SQL statement, with ADO, could look like

CurrentProject.Connection.Execute("SELECT COUNT(*) FROM
tblCoils").Fields(0).Value

Vanderghast, Access MVP

> Dim rsCurr As DAO.Recordset
>
[quoted text clipped - 56 lines]
>>> >
>>> > what would someone suggest?

Reply to this Message

onedaywhen - 02 Mar 2007 11:45 GMT

On Mar 1, 7:53 pm, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:

> An explicitly built SQL statement, withADO, could look like
>
> CurrentProject.Connection.Execute("SELECT COUNT(*) FROM
> tblCoils").Fields(0).Value

I suspect the table cardinality is *maintained* in the information
schema 'statistics', in which case retrieving it follows could be more
efficient than querying the table:

? CurrentProject.Connection.OpenSchema(adSchemaStatistics,
Array(Empty, Empty, " tblCoils")).Fields("CARDINALITY").Value

Jamie.

--

Reply to this Message