How can I delete all the records in a table?

[pjustin1]

Hi, I want to delete all the records in a table after sending these records to a report named as "MonthlyBalance" when I click a button. I written the following codes and I knew there is something wrong but I don't know where... can someone plse help me out with this? Here, I assume the field "BookTitle" will not contain "*" so all the records should be deleted...
Or is there a more correct way to delete all the records..?

Set rst = db.OpenRecordset("Select * From BalanceTable", dbOpenDynaset)
stDocName = "MonthlyBalance"
DoCmd.OpenReport stDocName, acPreview

wrk.BeginTrans
Do Until rst.EOF
If (rst!BookTitle <> "*") Then
rst.Delete
rst.Update
End If
rst.MoveNext
Loop
wrk.CommitTrans

Thanks!

 

[raskew]

A Delete Query is the ticket.
In the Access Help File, search on Delete Queries | Creating for more detailed information.

 

[PearlGI]

The following line will delete all records from the table.

db.Execute "DELETE * FROM BalanceTable"

HTH

 

[Pat Hartman]

I think you should reconsider this approach. You are going to end up deleting records before the report is even printed. You opened the report in preview mode and your VBA code will continue running. It will not wait for the report window to be closed. Why not just add a report date column to the table so that you can flag the date the records were reported. Then in a future process you can delete old data once you are sure that you no longer need it.