Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    47

    Unanswered: Delete top (n) rows

    Hi Group,
    How do I delete the top(n) rows of a table? Is this possible via VBA? Looking at the help file, says it can only be used for a Select and other types of queries but not the Delete query..

    Regards

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by axapta
    Hi Group,
    How do I delete the top(n) rows of a table? Is this possible via VBA? Looking at the help file, says it can only be used for a Select and other types of queries but not the Delete query..

    Regards
    There are a few ways to do this ... One is if there is a numeric ID where you can do something like:


    DELETE FROM SomeTable WHERE (MyID < 11);

    Assuming that you have record IDs 1 thru 10 ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You need to run two queries. One to select the records, and one to delete the selected records. Use the Top Values properties of the first query to get the records you want. The run a delete delete query to get rid of them.

    David

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    How about selecting the top n records into a recordset and then deleting them?

    Code:
     Dim rs As DAO.Recordset
     
     Set rs = CurrentDb.OpenRecordset("SELECT TOP 2 * FROM tblTest")
     
     Do While rs.EOF = False
         rs.Delete
         rs.MoveNext
     Loop

Posting Permissions

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