Deleting rows from a table using a loop

Question

Why does my Do Until loop try to run raw.Delete even though raw.EOF is true? If I have an empty table, this crashes. Why?

Dim raw As Recordset
Set raw = db.OpenRecordset("tblSampleRaw")

If raw.RecordCount > 0 Then
    raw.MoveFirst

    Do Until raw.EOF
        raw.MoveFirst
        raw.Delete
    Loop
End If

Raj More · Accepted Answer · 2009-11-11 17:13:46Z

3

You are doing a Row-By-Agonizing-Row or RBAR (reebar) operation. The larger the table, the more time this is going to take.

Most databases can work more efficiently than RBARs. I suggest you think in terms of SETS rather than rows.

I think you should replace that entire block of code with this:

DoCmd.RunSQL "DELETE * FROM tblSampleRaw"

for purposes of the answer

Dim raw As Recordset
Set raw = db.OpenRecordset("tblSampleRaw")

If raw.RecordCount > 0 Then
    raw.MoveFirst

    WHILE NOT raw.EOF or raw.BOF
        raw.MoveFirst
        raw.Delete
    Loop
End If

edited Nov 11, 2009 at 17:13

answered Nov 11, 2009 at 16:06

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joe Phillips Over a year ago

You are probably right. But a TRUNCATE would even be quicker. I'm just curious why this loop doesn't work though.

David-W-Fenton Over a year ago

If you're using Jet/ACE, it processes "DELETE * FROM table" as TRUNCATE.

David-W-Fenton Over a year ago

I think that inside the loop .MoveFirst should be .MoveNext and should be after the Delete.

onedaywhen Over a year ago

@David W. Fenton: What is the * in "DELETE * FROM Table" supposed to do. Delete all columns as opposed to... what?

Gratzy · Accepted Answer · 2009-11-11 16:06:52Z

2

I'm not sure, or a VBA expert, but how come you are constantly doing a MoveFirst? Your never moving forward in the recordset. Try

Do Until raw.EOF
        raw.Delete
        raw.MoveNext
 Loop

answered Nov 11, 2009 at 16:06

Gratzy

9,4174 gold badges32 silver badges45 bronze badges

7 Comments

Joe Phillips Over a year ago

Move to first record, delete it, then what is first? The next record I would hope

Gratzy Over a year ago

Well if that was true you would never need to do a MoveFirst then would you? You do it initially outside your loop so you should always be on the first record.

Gratzy Over a year ago

Also I don't think you hit EOF until you move passed the last record.

Philippe Grondier Over a year ago

you just took the words out of my mouth! +1

David-W-Fenton Over a year ago

The deleted record is not removed from the recordset unless you requery it. You can see this in a continuous or datasheet form in Access if you elsewhere delete a record displaying in the form the record selector and all the controls for that record will remain in the form, but with #DELETED in all the controls.

|

Chris · Accepted Answer · 2009-11-11 16:02:08Z

0

I'm not a VB programmer, but it looks like 'raw' is going to keep dropping through the loop even after it executes 'MoveFirst'.

answered Nov 11, 2009 at 16:02

Chris

6,3559 gold badges38 silver badges59 bronze badges

Comments

Fionnuala · Accepted Answer · 2009-11-11 16:02:26Z

0

How about Dim raw AS DAO.Recordset?

answered Nov 11, 2009 at 16:02

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Collectives™ on Stack Overflow

Deleting rows from a table using a loop

4 Answers 4

4 Comments

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related