PC Review


Reply
Thread Tools Rate Thread

Counting records in a Query using VBA

 
 
john.constantine@IFF.COM
Guest
Posts: n/a
 
      26th Mar 2009
I have looked through the help files but can't find anything -what I
want is the query equivalant to

CurrentDb.TableDefs("Mytable").RecordCount

Must be easy but has me stumped
 
Reply With Quote
 
 
 
 
Jeanette Cunningham
Guest
Posts: n/a
 
      26th Mar 2009
Hi john
Assuming the table has a primary key, you can go

Dim lngCount as Long
lngCount = DCount("*", "NameOfTable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have looked through the help files but can't find anything -what I
> want is the query equivalant to
>
> CurrentDb.TableDefs("Mytable").RecordCount
>
> Must be easy but has me stumped



 
Reply With Quote
 
 
 
 
Mr B
Guest
Posts: n/a
 
      26th Mar 2009
John,

If you are wanting a quick count of the records, look into then DCount
function.

Dim varTmp
varTmp = DCount("IdFieldInTable", "TableName")

If there is some reason you need to actually us a query, post back and we
will try to help.
--
HTH

Mr B
askdoctoraccess dot com


"(E-Mail Removed)" wrote:

> I have looked through the help files but can't find anything -what I
> want is the query equivalant to
>
> CurrentDb.TableDefs("Mytable").RecordCount
>
> Must be easy but has me stumped
>

 
Reply With Quote
 
Hans Up
Guest
Posts: n/a
 
      26th Mar 2009
(E-Mail Removed) wrote:
> I have looked through the help files but can't find anything -what I
> want is the query equivalant to
>
> CurrentDb.TableDefs("Mytable").RecordCount


SELECT Count(*) AS num_records FROM Mytable;
 
Reply With Quote
 
Roger Carlson
Guest
Posts: n/a
 
      26th Mar 2009
You were close. The fastest way to get the record count of a table is:

CurrentDb.OpenRecordset("Mytable").RecordCount

The thing is, here it HAS to be a LOCAL table to return the correct record
count. If it is a linked table or a query, it will always return 1 as the
record count.

The fastest way to get the record count of a linked table is this:

Dim db As DAO.Database
Set db = OpenDatabase("c:\My Documents\LinkedDatabase.mdb")
db.OpenRecordset("Mytable").RecordCount

I suppose you could put it all on one like like this:
OpenDatabase("c:\My
Documents\LinkedDatabase.mdb").OpenRecordset("Mytable").RecordCount


On a table with over 7 million records, both of these returned the record
count in 1 second or less. Every other method mentioned will be an order of
magnitude slower. (Disclaimer: I didn't check ADO methods.)


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have looked through the help files but can't find anything -what I
> want is the query equivalant to
>
> CurrentDb.TableDefs("Mytable").RecordCount
>
> Must be easy but has me stumped



 
Reply With Quote
 
UpRider
Guest
Posts: n/a
 
      26th Mar 2009
John, use this function:

Function fcnCountQueryRecs(qQuery) As Long
fcnCountQueryRecs = DCount("*", qQuery)
End Function

qQuery is a string, the query name.

HTH, UpRider

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have looked through the help files but can't find anything -what I
> want is the query equivalant to
>
> CurrentDb.TableDefs("Mytable").RecordCount
>
> Must be easy but has me stumped


 
Reply With Quote
 
Jeanette Cunningham
Guest
Posts: n/a
 
      27th Mar 2009
Bookmarked for future reference

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Roger Carlson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You were close. The fastest way to get the record count of a table is:
>
> CurrentDb.OpenRecordset("Mytable").RecordCount
>
> The thing is, here it HAS to be a LOCAL table to return the correct record
> count. If it is a linked table or a query, it will always return 1 as the
> record count.
>
> The fastest way to get the record count of a linked table is this:
>
> Dim db As DAO.Database
> Set db = OpenDatabase("c:\My Documents\LinkedDatabase.mdb")
> db.OpenRecordset("Mytable").RecordCount
>
> I suppose you could put it all on one like like this:
> OpenDatabase("c:\My
> Documents\LinkedDatabase.mdb").OpenRecordset("Mytable").RecordCount
>
>
> On a table with over 7 million records, both of these returned the record
> count in 1 second or less. Every other method mentioned will be an order
> of magnitude slower. (Disclaimer: I didn't check ADO methods.)
>
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I have looked through the help files but can't find anything -what I
>> want is the query equivalant to
>>
>> CurrentDb.TableDefs("Mytable").RecordCount
>>
>> Must be easy but has me stumped

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Records in a Query Using Code Amy Microsoft Access Form Coding 1 13th Apr 2009 11:39 PM
using vba to loop an insert query until all records in query are d ArielZusya Microsoft Access Form Coding 4 11th Jan 2008 05:31 PM
Nslookup query for NS records returns all of the NS records, but not all of the Host records Bob Microsoft Windows 2000 Networking 1 8th Nov 2004 08:03 PM
Nslookup query for NS records returns all of the NS records, but not all of the Host records Bob Microsoft Windows 2000 DNS 2 7th Nov 2004 05:42 AM
Excel VBA - Cells Counting using VBA codes and displaying results in msg box wuming Microsoft Excel Programming 6 9th Jul 2004 03:18 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:44 PM.