Delete Query problem

[dcnick]

Hello.

I am setting up a button on a form to delete all records from a table and refresh the form. I have set up a delete query to do so.

If I run the query by itself, it deletes all records in the table as intended. However, if I call the query from the form, it deletes all but one record. Any ideas? Here is my code.

Thanks in advance
dcnick

DoCmd.OpenQuery ("qryCleartblRunMatrix")
Me.Requery

 

[RoyVidar]

One guess would be that there's unsaved changes in the current record, therefore you're not allowed to delete it.

What happens if you throw a docmd.runcommand accmdsaverecord prior to running the query?

 

[dcnick]

That fixed it. Thanks so much!

 

[MarkK]

What about...

With Me.Recordset
``Do While Not .EOF
````.Delete
````.MoveNext
``Loop
End With

 

[pbaldy]

I suspect testing would reveal that executing the delete query is more efficient than the recordset loop. Also, I'd execute it with CurrentDb.Execute rather than DoCmd.

 

[RoyVidar]

Yup - but I must confess to being a little surprised at how poor the recordset approach really was. For 40 000 records with just two fields (Autonumber - PK, Long Ingteger), the recordset approach used 1 min 36 seconds on my setup, while executing a query wasn't measurable using Now as timer -> less than one second

 

[MarkK]

Wow. That's a HUGE difference. Thanks for the observations.

 

[dcnick]

pbaldy -

I've tried it with both DoCmd and CurrentDb.Execute, and they both work. Can you explain the difference and why one is preferred over the other?

Thanks

 

[pbaldy]

I'm not qualified to speak on the technical differences, I've just seen testing and tested myself, and CurrentDb.Execute comes out ahead speed-wise (often way ahead). It also doesn't require you to set warnings false/true before and after it.

 

[dcnick]

Great. My database isn't large, so I couldn't tell a difference in time...thanks for the info.