Running a count query from VBA.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
..
 
Excellent, thank you for the assistance.

Douglas J. Steele said:
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
Click to expand...
 
Back
Top