getting current record count in a table

[Guest]

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?

 

[Michel Walsh]

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

 

[Guest]

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

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

 

[Michel Walsh]

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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

 

[onedaywhen]

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.

--