Yes, select statements are always used in recordsets.
to use the connection you created, you should create a recordsety that uses that connection, that will generate the code to use your connection.
in the recordset, go to advanced view, and enter the SQL statement:
SELECT COUNT(idColumnNAme) as recordCount FROM tableName
where idColumnNAme is the name of the ID column in your table and tableName is the name of the table.
to echo the result to the page, open the bindings panel, expand the recordset, and add the column binding to your page from the recordset