By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,220 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,220 IT Pros & Developers. It's quick & easy.

counting recs in a table?

P: n/a
MLH
If you're only furnished the table
name of a table in your database,
what can I do in VBA to count tablle
records in a given table? Seeking
simplest method.

In other words, I don't have the name
of a field to furnish to DLookUp -
all I have is a table name.
Jul 31 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Try:
=DCount("*", "MyTable")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:r0********************************@4ax.com...
If you're only furnished the table
name of a table in your database,
what can I do in VBA to count tablle
records in a given table? Seeking
simplest method.

In other words, I don't have the name
of a field to furnish to DLookUp -
all I have is a table name.

Jul 31 '06 #2

P: n/a
DCount(1, "Order Details")

--
Lyle Fairfield
Jul 31 '06 #3

P: n/a

MLH wrote:
If you're only furnished the table
name of a table in your database,
what can I do in VBA to count tablle
records in a given table? Seeking
simplest method.

In other words, I don't have the name
of a field to furnish to DLookUp -
all I have is a table name.
Not quite sure what you're after here.

If it is simply a count of the records in VBA then the simplest method
is to open a recordset based on the table in question, and add a
Debug.Print rs.Recordcount where rs is the recordset.

If however you are looking for the individual values within that table
then you should modify your sql code to SELECT DISTINCT and the field
name which you want to know.

Good luck

Nick

Jul 31 '06 #4

P: n/a
You would either use the DCount function or open a recordset where you

SELECT count(*) FROM YourTable

not use the recordcount property of a recordset at all.

--

Terry Kreft
"Nick 'The Database Guy'" <ni*****@btinternet.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>
MLH wrote:
If you're only furnished the table
name of a table in your database,
what can I do in VBA to count tablle
records in a given table? Seeking
simplest method.

In other words, I don't have the name
of a field to furnish to DLookUp -
all I have is a table name.

Not quite sure what you're after here.

If it is simply a count of the records in VBA then the simplest method
is to open a recordset based on the table in question, and add a
Debug.Print rs.Recordcount where rs is the recordset.

If however you are looking for the individual values within that table
then you should modify your sql code to SELECT DISTINCT and the field
name which you want to know.

Good luck

Nick

Jul 31 '06 #5

P: n/a
MLH
Perfect! That's it. Thanks a lot
Jul 31 '06 #6

P: n/a
I did this for my own interest. Perhaps someone else would like to see.
I suppose the knowledge would be useful only with a table with many
many records. Of course we shouldn't assume that 10000 counts of 1522
records is the same as one count 15220000 records. There are other
things going on inside these loops.

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Const Iterations As Long = 10000
Sub HowManyRecords()
Dim c As Long
Dim t As Long
Dim z As Long

t = GetTickCount
For z = 1 To Iterations
c = DCount("*", "Order Details")
Next z
Debug.Print "DCount *", GetTickCount - t

t = GetTickCount
For z = 1 To Iterations
c = DCount(1, "Order Details")
Next z
Debug.Print "DCount 1", GetTickCount - t

t = GetTickCount
For z = 1 To Iterations
c = DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) FROM [Order
Details]").Collect(0)
Next z
Debug.Print "DAO SELECT COUNT(*)", GetTickCount - t

t = GetTickCount
For z = 1 To Iterations
c = CurrentProject.Connection.Execute("SELECT COUNT(*) FROM
[Order Details]").Collect(0)
Next z
Debug.Print "ADO SELECT COUNT(*)", GetTickCount - t

t = GetTickCount
For z = 1 To Iterations
c = DBEngine(0)(0).TableDefs("Order Details").RecordCount
Next z
Debug.Print "TableDef.DistinctCount", GetTickCount - t

t = GetTickCount
For z = 1 To Iterations
c = DBEngine(0)(0).TableDefs("Order
Details").Indexes("PrimaryKey").DistinctCount
Next z
Debug.Print "PrimaryKey.DistinctCount", GetTickCount - t

End Sub

First Run:
DCount * 16614
DCount 1 16794
DAO SELECT COUNT(*) 10285
ADO SELECT COUNT(*) 22632
TableDef.DistinctCount 60
PrimaryKey.DistinctCount 80

Second Run:
DCount * 15943
DCount 1 15693
DAO SELECT COUNT(*) 9834
ADO SELECT COUNT(*) 21301
TableDef.DistinctCount 60
PrimaryKey.DistinctCount 70

Aug 2 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.