UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> GetSQLRecordcount    

The built-in DCount function gives us the means to return a recordcount of particular search criteria for a table or saved query without parameters. This simple function enables us to return the record count of any SQL string that returns a recordset.

The function will return 0 for erroneous SQL statements so be sure to proof SQL in development before distribution. All errors are displayed to the user.

CODE

' Code courtesy of UtterAccess Wiki
' http://www.utteraccess.com/wiki/index.php/Category:FunctionLibrary
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' REV  DATE                          DESCRIPTION
' 1.0  2010-08-16              initial release
' 1.1  2010-09-12              modified function header
'
'==============================================================================
' NAME: GetSQLRecordcount
' PURPOSE: returns the recordcount of a non-action query
' RETURNS: Long, number of records in the SQL passed, 0 on error
' ARGUMENTS: strSQL: SQL Statement to evaluate
' USAGE: first word must be "SELECT" (case-insensitive)
'        current handling for CurrentDb only
'
' DEPENDANCIES: DAO
'
' REVISIONS:
'  REV |    DATE    | REV TYPE | DESCRIPTION
'------------------------------------------------------------------------------
'  R01   8/16/2010    INITIAL
'
'==============================================================================
'ErrHandler V3.01
Public Function GetSQLRecordcount(strSQL As String) As Long
On Error GoTo Error_Proc
Dim Ret As Long
'=========================
 Const ERRN_SQL_NOTSELECT = 8000 + vbObjectError
 Const ERRM_SQL_NOTSELECT As String = _
   "The SQL is not a Select statement."

 Dim rs As DAO.Recordset
'=========================

 'make sure the statement starts with select
 If StrComp(Left(LTrim(strSQL), 6), "SELECT", vbTextCompare) <> 0 Then
   'raise error
   Err.Raise ERRN_SQL_NOTSELECT, , ERRM_SQL_NOTSELECT
 End If

 Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM (" & strSQL & ") As vTbl")
 
 If rs.EOF Then
   Ret = 0
 Else
   Ret = rs.Fields(0)
 End If
 
 rs.Close
 
'=========================
Exit_Proc:
 Set rs = Nothing
 GetSQLRecordcount = Ret
 Exit Function
Error_Proc:
 Select Case Err.Number
   Case Else
     MsgBox "Error: " & Trim(str(Err.Number)) & vbCrLf & _
       "Desc: " & Err.Description & vbCrLf & vbCrLf & _
       "Module: modSQLUtil, Procedure: GetSQLRecordcount" _
       , vbCritical, "Error!"
 End Select
 Resume Exit_Proc
 Resume
End Function


Creative Commons License
GetSQLRecordcount by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 4,541 times.  This page was last modified 20:03, 11 September 2012 by Brent Spaulding. Contributions by Jack Leach  Disclaimers