Running a count query from VBA.

[Guest]

Hi, I wish to run the following query in VBA and get the its value into a
variable:

SELECT Count(TBL_Students.students) AS CountOfStudents
FROM TBL_Students;

I know typically you can only run action queries in Access but there should
be a way to run this. I wan the count value to perfom some logic.

Thanks in advance.

 

[Douglas J. Steele]

Simplest approach would be to use the DCount function instead:

DCount("Students", "TBL_Students")

Alternatively, you need to open a recordset and retrieve the value from it.
The following shows how to do this using a DAO recordset:

Dim rsCurr As DAO.Recordset
Dim lngCountOfStudents As Long
Dim strSQL As String

strSQL = "SELECT Count(TBL_Students.students) " & _
"AS CountOfStudents FROM TBL_Students"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
lngCountOfStudents = rsCurr!CountOfStudents
rsCurr.Close
Set rsCurr = Nothing

 

[John Spencer]

TheCount = Currentdb().OpenRecordset("SELECT Count(*) FROM
tblStudents").Fields(0)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Excellent, thank you for the assistance.

Simplest approach would be to use the DCount function instead:

DCount("Students", "TBL_Students")

Alternatively, you need to open a recordset and retrieve the value from it.
The following shows how to do this using a DAO recordset:

Dim rsCurr As DAO.Recordset
Dim lngCountOfStudents As Long
Dim strSQL As String

strSQL = "SELECT Count(TBL_Students.students) " & _
"AS CountOfStudents FROM TBL_Students"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
lngCountOfStudents = rsCurr!CountOfStudents
rsCurr.Close
Set rsCurr = Nothing