 jdumayas wrote:
Hello,
I'm using the following line of code to return the number of students are registered for a course.
rs.Open "SELECT COUNT(courseID) FROM tblCourseStudentLookup WHERE courseID="&instanceNo, adoCon
|
As far as the SQL string is concerned, always try to use "*" with Count function as this is heavily optimised in Access using the built in Rushmore technology and will therefore speed up processing.
Also, your code snippet appears to be missing a name for the returned result so will default to Expr1; a field name should really be enclosed in square brackets; use spaces between the quote ("), ampersand (&) and instanceNo; and terminate the SQL expression with a ";"
Your example rewritten
rs.Open "SELECT COUNT([courseID]) As NumRecs FROM tblCourseStudentLookup WHERE courseID=" & instanceNo & ";", adoCon
Same optimised
rs.Open "SELECT COUNT(*) As NumRecs FROM tblCourseStudentLookup WHERE courseID=" & instanceNo & ";", adoCon
Sorry, don't have time to test it but should work
Topic Options
Post Options
Thanks(0)
