In a nutshell:  



  I am unable to limit the size of a recordset being

  returned from an ADO query (cmd obj) 



  "SELECT TOP 5 ..." syntax appears to be being ignored

  and returns ALL matching records instead of just 5

  

  Front and back ends are both MS Access 2000 .MDBs

  

  Everything else appears to working as expected.



  Additional detail below, please let me know if there

  is any other information that might help you point

  me in the right directions. Thanks.

  



Connection string:                



  Public Const gd_ADOconnstr = 

     "Driver={Microsoft Access Driver (*.mdb)};" & _

     "Dbq=\\....."

     

The Code:



      -----------------------------------



      'Build the SQL statement for the form's recordsource

      strSQL = "SELECT TOP 5 " & _

               "CustomerID," & _

               "DateCreated," & _

               "BranchID," & _

               "CurPgm," & _

               "CurSeq," & _

               "NextContact," & _

               "FirstName," & _

               "MI," & _

               "LastName," & _

               "Address1," & _

               "Address2," & _

               "City," & _

               "State," & _

               "Zip," & _

               "City & ', ' & State & '   ' & Zip AS CSZ," & _

               "DayPhone, " & _

               "EveningPhone, " & _

               "Comments " & _

               "FROM APPLICATIONS " & _

               "WHERE ACTIVE=True AND DONOTCALL=False AND "

               "         CurPgm=" & gd_CurrentProgramID & " AND " & _

               "         NextContact<#" & Now() & "# " & _

               "ORDER BY NextContact;"

  

      'Open ADO recordset

      If rs.STATE <> adStateClosed Then

        rs.Close

      End If

      Set cnn = New ADODB.Connection

      cnn.Open gd_ADOconnstr

      rs.CursorLocation = adUseClient

      rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic



      

      'disconnect the recordset

      rs.ActiveConnection = Nothing

      cnn.Close

      If Not rs.EOF Then



      --------------------------

I don't know if this is what is happening, but remember that Top can

return a lot more records than the number requested because it is

returning the top values, not records. So for instance, if twenty

records have the same value and that is the Top value, those 20 records

will be returned and counted as only one value. Check to see how many

different values are being returned rather than how many records.

Hope this helps.



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx

Richard:



That was precisely the problem. Thank you very much.



Because my table was filled with newly imported records, the

"NextContact" field (in my ORDER BY clause) were all set to the

same value -- which is what the client wants upon import.



To solve the the problem, I simply added the record's primary

key to the ORDER BY clause:



        ORDER BY NextContact, CustomerID



Now my SELECT TOP statement works exactly as expected.



Thanks for taking the time to help.



Tim Nemec

Tom,



You're welcome. Glad I could help.



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx