Efficient Count method

Results 1 to 2 of 2

Thread: Efficient Count method

  1. #1
    Spooky Guest

    Default Efficient Count method

    Hi all,<BR><BR>Whats the most efficient way to count records in an Access db?<BR><BR>Currently I am opening and closing an ado connection for each count using sql : SELECT Count(*) AS fieldcount FROM Rs <BR><BR>As all the records are different sql variations on the same table, is there a better way to use the connection?<BR><BR>Cheers.

  2. #2
    Join Date
    Dec 1969

    Default RE: Efficient Count method

    Open one connection only.<BR><BR>Dim objConn<BR>Set objConn = Server.CreateObject("ADODB.Connection")<BR>objConn .Open "DSN=Blah"<BR><BR>&#039Create a recordset<BR>Dim objRS<BR>Set objRS = Server.CreateObject("ADODB.Recordset")<BR><BR>&#03 9Create count variables<BR>Dim tbl1Count, tbl2Count, etc...<BR><BR>objRS.Open "SELECT COUNT(*) FROM tbl1", objConn<BR><BR>tbl1Count = CInt(objRS(0))<BR><BR>objRS.Close<BR>objRS.Open "SELECT COUNT(*) FROM tbl2", objConn<BR>tbl2Count = CInt(objRS(0))<BR><BR>objRS.Close<BR>...<BR><BR>ob jRS.Close<BR>Set objRS = Nothing<BR><BR>objConn.Close<BR>Set objConn = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts