How do I clear records out of a Table in Access using a button?

Question

Hello all.

This has been pretty frustrating. I'd have thought this would have been one of the simplest and easiest things to do. I've found a lot of examples online and none of which has done what I wanted to do.

I have a couple of import buttons that run "saved imports" they append to different tables.

What I would like is to have a 'DELETE' or 'CLEAR' table query to run just before those imports do.

I setup a button to delete each of the tables and have tried the following code but no luck on working:

Private Sub Command99_Click()
****ATTEMPT 1****
'DoCmd.DeleteObject acTable, "tbl_NEOCOP_PartPriority"
'MsgBox "NEOCOP Part Priority Table Deleted Successfully!"
****ATTEMPT 2****
'DoCmd.RunSQL
'DELETE *
'FROM tbl_NEOCOP_PartPriority;
****ATTEMPT 3****
'DoCmd.RunSQL  "DELETE * FROM tbl_NEOCOP_PartPriority"
End Sub

Answer 1

Hi,

Assuming the comment marks were just there because you tried the lines and comment them out after they didn't work, what happened instead? Did you get an error? If so what was the error message?

Answer 2

There is a difference between deleting the table object (which is RARE, and I would not recommend), and the data in the table (your second attempt). That should have worked. What is the error message?

Reasons it would not work include records that are in use and thus locked, and child records in another table protected by referential integrity.

---

-Tom. Microsoft Access MVP

Answer 3

If the other better and more likely answers don't help.

You might have the problem I first ran across when I first started to clear a table using code.

That was the "primary key" field was an autoID field. 

And sometimes that created a problem when I wanted to empty one of my tables that I would fill and empty several times as the procedure ran. 

Also sometimes, after my code emptied a table of 500 records without a problem, the table ID started at 501 for the first "New" record.

You might have to add some code if you want or need a number in the "ID" or "Primary Key" field.

This issue does occur sometimes.

Mark J

---

Mark J

Answer 4

Answer 5

I am simply trying to delete records in a table. Not the table itself.

Answer 6

I am simply trying to delete records in a table. Not the table itself.

Hi,

If you close all your db objects and open only the table, are you able to manually delete from it without any errors?

If so, try closing all the objects again and run the following code in the Immediate Window and let us know if you get any errors.

CurrentDb.Execute "DELETE FROM tbl_NEOCOP_PartPriority", dbFailOnError

Answer 7

You only need to execute the SQL statement, e.g.

    Const conSQL = "DELETE * FROM tbl_NEOCOP_PartPriority"
    CurrentDb.Execute conSQL, dbFailOnError

For this to work it is important that, if this is a referenced table in a one-to-many relationship type, and referential integrity is enforced, as it should be, cascade deletes are also enforced in the relationship to delete any rows in the referencing table.  Before doing this, however, ask yourself if this is really what you want to happen.  This will delete all the data, not only from the referenced table, but also from the referencing table.  This is perfectly legitimate in the right context, but in another context you might not want that to happen, but to prevent the rows from the referenced table, tbl_NEOCOP_PartPriority, being deleted if there are any matching rows in the referencing table.  So, the enforcement of cascade deletes is a matter of judgement on your part.

Note that a referencing table might be a table which models a many-to-many relationship type, e.g. an OrderDetails table models a many-to-many relationship type between Orders and Products tables.  If cascade deletes are enforced in the relationship between Orders and OrderDetails, but not in the relationship between Products and OrderDetails, then deleting rows from Orders would also delete rows from OrderDetails, but not from Products.

---

Ken Sheridan, Stafford, England

Answer 8

The error message might mean that you currently have the table or a form bound to the table open at a record which has been edited, but not saved to the table.   Before deleting the rows ensure that the table and any forms which include it in their recordset are closed.

---

Ken Sheridan, Stafford, England

Answer 9

Thank you Ken! This is exactly what I needed to know!!

Cheers,

Josh

Answer 10

Hello stillanoob,

It seems that your issue has been resolved. If so, I would suggest you mark the helpful reply to close the thread. If not, please feel free to detail us your current issue.

Besides, I note that you have also post some other thread which is not closed. Could you please tell us the current of below threads?

How can I copy A word Documents information into Excel?

Why isn't a pre-existing Excel macro capturing date fields from SQL tables?

How to apply a recorded macro to another worksheet?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].