We help IT Professionals succeed at work.

SQL and resultset to count records with MS Access Database

etstiles asked
I am connecting to a Microsoft Database using the JDBC-ODBC Driver.  I would like to get the record count using the following SQL command:

'SELECT COUNT (*) from Table'

How can I set the return value to a variable.  Please be explicit.  The better the answer and I will give more points.

The only other way that I can get the record count is by getting the resultSet and using next() in a while loop to scroll through the resultset and using a counter to count the numbers of loops.  This seems like a very inefficient method, because then I also need to keep track of the record before I got the record count and then I have to re-sql the table to scroll forward to the record where I was before I got the recordcount.

If there is a better way, that works, then what I am asking for please also tell me.

Using last() returns an error telling me that the record is FORWARD_ONLY.   Using next() by itself and then trying to use getRow() will return 0 because next() always scrolls past the end of the resultset.  Using isLast() returns the same error as last().

Watch Question


In the case of your select statement, the result set will not loop more than one time. So you can replace the loop with :

resultSet.next(); // move the pointer to the first row
int count = resultSet.next();//retrieve the first row and move the pointer to the next row
1. Use an AS clause:
SELECT COUNT(*) AS 'cnt' FROM table
and get value:
iRecordCount = rs.getInt("cnt");

2. For last() and etc. use a suitable driver (http://industry.java.sun.com/products/jdbc/drivers)

ResultSet rs = stmt.executeQuery("Select count(*) from table");

int count = 0;

if (rs.next()) {
   count = rs.getInt(1); // get the count


Thanks.  I thought it was something like this, but I couldn't find an example to help get through the problems.

Thank you :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.