DCount Problem

[Jeff via AccessMonster.com]

Is there a faster way to retrieve the record count of a given criteria

---without using DCount---

---without looping through a recordset object---

For example:

I do NOT want to do this:
---
myVariable= DCount("myField", "MyTable", "myCriteria")
---

and I do NOT want to do this:
---
dim db as database, rs as recordset, myVariable as long
set db=currentdb
set rs=db.openrecordset("select * from myTable where myCriteria")
with rs
.movelast
.movefirst
myVariable = .recordcount
end with
rs.close
set rs=nothing
db.close
set db=nothing
---

?? Any suggestions?

Thank you,
Jeff

 

[Allen Browne]

Perhaps:
Set rs = db.OpenRecordset("SELECT Count([Field1]) AS TheCount FROM
Table1 WHERE " & myCriteria)
Debug.Print rs!TheCount

The domain aggregate functions in Access are notoriously ineffient, and
unnecessariliy limited, so there's an ECount() that takes the approach above
here:
http://allenbrowne.com/ser-66.html

 

[Klatuu]

The Dcount is the better way to do it, but you don't need the criteria or to
identify a field. Just:
myVariable= DCount("*", "MyTable")

 

[Jeff via AccessMonster.com]

Is there an ELookup() function somewhere?

Perhaps:
Set rs = db.OpenRecordset("SELECT Count([Field1]) AS TheCount FROM
Table1 WHERE " & myCriteria)
Debug.Print rs!TheCount

The domain aggregate functions in Access are notoriously ineffient, and
unnecessariliy limited, so there's an ECount() that takes the approach above
here:
http://allenbrowne.com/ser-66.html

Is there a faster way to retrieve the record count of a given criteria

[quoted text clipped - 29 lines]

Thank you,
Jeff

 

[Allen Browne]

Yep; see:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there an ELookup() function somewhere?

Perhaps:
Set rs = db.OpenRecordset("SELECT Count([Field1]) AS TheCount FROM
Table1 WHERE " & myCriteria)
Debug.Print rs!TheCount

The domain aggregate functions in Access are notoriously ineffient, and
unnecessariliy limited, so there's an ECount() that takes the approach
above
here:
http://allenbrowne.com/ser-66.html

Is there a faster way to retrieve the record count of a given criteria

[quoted text clipped - 29 lines]

Thank you,
Jeff

 

[Jeff via AccessMonster.com]

Thank you, Allen...

Wondering if you could help me with something else:

Were sharing a large backend database across multimple computers.

When this backened database is opened by more than one computer, the ECount,
and ELookup function execute slowly: (about three seconds each). Several "E"
functions are being called during the _load event of our startup form.

When only ONE of the Front-end's are open, the E function execute at
lightning speed.

Do you know of a reason why it runs slow when the backend is open by more
than one machine? And what to do to work around that?

Yep; see:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html

Is there an ELookup() function somewhere?

[quoted text clipped - 14 lines]

 

[Allen Browne]

Tony Toews has an excellent list of things to work through for performance
problems:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Jeff via AccessMonster.com]

Thank you
Thank you
Thank you!

Allen: Your my new best friend!

Tony Toews has an excellent list of things to work through for performance
problems:
http://www.granite.ab.ca/access/performancefaq.htm

Thank you, Allen...

[quoted text clipped - 13 lines]

Do you know of a reason why it runs slow when the backend is open by more
than one machine? And what to do to work around that?