Delete all data from Acces

Question

Hello guys,

I'm working with access database in my c# application. How can I make a button to delete all data from all tables just by one click?
And also I want to know what should I do when my database get full?

---

ES

Answer 1

I would look at the second VBA example at the below link, which simply cycles through all of TableDefs and deletes all of the rows:

https://stackoverflow.com/questions/685112/how-to-delete-data-in-all-ms-access-tables-at-once

Access has a 2 GB limitation. You will want to periodically compact the database, either through Microsoft Access or through VBA code (CompactDatabase). If your database becomes too large you can split it up into multiple databases and create linked tables to the tables in the second database. In many instances Access developers will do this ahead of time by creating the UI in one database and putting all of the tables containing the data in one or more other databases.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

>  simply cycles through all of TableDefs and deletes all of the rows:

I surely hope that would not work, because referential integrity would intervene. Either keep an empty database on-hand or cycle through the tabledefs in a proscribed order, so that for example the OrderDetails are deleted before the Orders.

---

-Tom. Microsoft Access MVP

Answer 3

>  simply cycles through all of TableDefs and deletes all of the rows:

I surely hope that would not work, because referential integrity would intervene. Either keep an empty database on-hand or cycle through the tabledefs in a proscribed order, so that for example the OrderDetails are deleted before the Orders.

---

-Tom. Microsoft Access MVP

Good point. Yes, if referential integrity has been implemented then this solution might be problematic. Otherwise, it should work fine.

The empty database method was the first option suggested in the link I posted and might be the easier option.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

Hi dokirt.2,

If your data are stored in relationship and you want to delete data then you need to set 'Cascade Delete Related Records'.

Follow these steps only when you need to delete data on the "one" and "many" sides of a relationship.

(1) On the Database Tools tab, in the Relationships group, click Relationships.
(2) Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Edit

Relationship on the shortcut menu.

The Edit Relationships dialog box appears.
(1) Ensure that the Enforce Referential Integrity check box is selected.
(2) Select the Cascade Delete Related Records check box.

Note:  Until you disable this property again, deleting a record on the "one" side of the relationship will delete all of the related records on the "many" side of the relationship.

Reference:

Delete data from an Access database by using a query

I agree on the suggestion to replace the current database with empty database will be the easiest solution.

Other thing you had mentioned that,"what should I do when my database get full?"

First , It depends what kind of Data and How much data you are trying to store in your database.

If you are storing image and files then it will reach the limit of 2 GB very soon.

For that , you can try to store path of the files in database.

Then you will be able to store much more data.

You can try to take backup timely so you can copy or move your data that you are not going to use again and then after you can use that empty space to store your new data.

You always have backup, so when ever you want to retrieve old data then you can easily get it from backup.

I did not understand the logic behind deleting the all the data.

Is that data is useless? generally people try to take a backup of the data for the future.

If i misunderstand any thing then try to correct me.

Let us know , If you have any further questions.

We will try to provide further suggestions to solve the issue.

Regards

Deepak

---

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].

Answer 5

> you need to set 'Cascade Delete Related Records'.

Noooooo, that is a really bad idea, unless it is supported by the business rules of the application.

"We hired a new CFO and when she accidentally deleted a customer she thought was a duplicate, all their orders and order details were deleted as well". That could be a lot of trouble for the developer.

"Clear out all data" is an unusual request so likely not a standard order entry system, but I wanted to sound the alarm that blindly applying cascade delete is in most databases a violation of (implicit) business rules.

---

-Tom. Microsoft Access MVP

Answer 6

I am not as experienced with Access as most others that are likely to make suggestions but I assume it is possible to create a new database. So you just delete or rename the old one and create a new blank database.

---

Sam Hobbs
SimpleSamples.Info