Results 1 to 7 of 7

Thread: Delete (2003)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete (2003)

    How can I change the following to delete all but the last row instead of skipping the first data point and deleting the rest? I have the SQL statement set up so that only similar data points are pulled into rs. Thanks.

    . . .
    rs.MoveNext

    Do Until rs.EOF
    On Error Resume Next
    rs.Delete
    rs.MoveNext
    Loop

    . . .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2003)

    Move backwards:

    rst.MoveLast
    Do While Not rst.BOF
    rst.MovePrevious
    If rst.BOF Then Exit Do
    rst.Delete
    Loop

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2003)

    I see the logic here, but I do not understand why it does the following.

    Line1
    Line2
    Line3
    Line4

    I have four lines of data, three of which should be deleted. When I run the above code, Line3 and Line1 are deleted and Line1 and Line4 remain. I need everything deleted except Line4. Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2003)

    It worked correctly in a test I did, so without seeing your database, I have no idea either.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete (2003)

    Are you sure you copied the code correctly? Hans's code works perfectly for me.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (2003)

    Could you attach a copy of your database example that you used? Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete (2003)

    I hadn't kept anything, but here is a demo database (Access 2000 format, zipped). The module contains three procedures:

    CopyTable: makes a fresh copy of tblOriginal, so that you can test several times.
    DeleteAllButLast: the procedure that deletes all records but the last one.
    Test: calls CopyTable first, then DeleteAllButLast. Run this one to test the code.

Posting Permissions

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