Deleting data from an Access table
celine barriant
-
nabil -
nabil -
Hello,
I have created a database on Access containing several tables. My problem is that I did not plan a way to delete data in the fields of the different tables. To remedy this, I would like to create a delete button.
This button should also be able to delete data that is related in the other tables.
Please let me know how to do this because I am currently stuck.
Céline Barriant
I have created a database on Access containing several tables. My problem is that I did not plan a way to delete data in the fields of the different tables. To remedy this, I would like to create a delete button.
This button should also be able to delete data that is related in the other tables.
Please let me know how to do this because I am currently stuck.
Céline Barriant
11 answers
Hi,
To solve your problem, you have two options:
1) Through the relationship diagram, you specify for each relationship that referential integrity must be preserved. This means that if you delete a record in a "parent" table, all corresponding items in the "child" tables will also be deleted. To do this, double click on the link (you need to aim well).
2) You can code in VBA what your button should do.
Example:
Nothing is more dangerous than an idea when you only have one :-)
To solve your problem, you have two options:
1) Through the relationship diagram, you specify for each relationship that referential integrity must be preserved. This means that if you delete a record in a "parent" table, all corresponding items in the "child" tables will also be deleted. To do this, double click on the link (you need to aim well).
2) You can code in VBA what your button should do.
Example:
Private Sub Ton_Bouton_Click() Dim i As Long Dim mDb As Database Dim mRs, mRsFiltred As Recordset Set mDb = CurrentDb Set mRs = mDb.Openrecordset("Nom_Table", dbOpenDynaset, dbSeeChanges, dbPessimistic) mRs.Filter = "Criteria for records to delete" Set mRsFiltred = mRs.Openrecordset mRsFiltred.MoveLast For i = 1 To mRsFiltred.RecordCount mRsFiltred.AbsolutePosition = i mRsFiltred.Edit mRsFiltred.Delete mRsFiltred.Update Next i End Sub Nothing is more dangerous than an idea when you only have one :-)
When you create your button, you should have the assistant offer you different types of actions. Choose to delete the record.
Then in the relationship tool, you should be able to specify that when you delete from the main table, it deletes the fields from the other table in cascade.
Sorry if this is incorrect or imprecise, I'm telling you this off the top of my head, I don't have Access installed on this workstation.
Well, I hope I haven't messed up and that I’ve helped you.
See you soon
;-)
~ ~ Fada ~ ~ : Member of Star!alco
Then in the relationship tool, you should be able to specify that when you delete from the main table, it deletes the fields from the other table in cascade.
Sorry if this is incorrect or imprecise, I'm telling you this off the top of my head, I don't have Access installed on this workstation.
Well, I hope I haven't messed up and that I’ve helped you.
See you soon
;-)
~ ~ Fada ~ ~ : Member of Star!alco
Here is the first solution, I had already applied referential integrity to my relationships, and when I create a delete button using the wizard, I get this error message:
Unable to delete or modify the record because the table "ETABLISSEMENT" has related records
For the second solution, it says "compilation error: method or data member not found" I don’t even know what that could mean...
Unable to delete or modify the record because the table "ETABLISSEMENT" has related records
For the second solution, it says "compilation error: method or data member not found" I don’t even know what that could mean...
I think I'm going to use the somewhat brute-force solution I wanted to avoid. For deletion, it’s not cascading updates that are important but cascading deletes.
I know that this functionality is tricky to use because it sometimes leads to unforeseen consequences, and I no longer remember what they are.
And another question: what is the difference between deleting data in a field and canceling data in a field?
Thank you.
I know that this functionality is tricky to use because it sometimes leads to unforeseen consequences, and I no longer remember what they are.
And another question: what is the difference between deleting data in a field and canceling data in a field?
Thank you.
The first problem is related to referential integrity....
The delete button refuses to work because, it seems, it cannot delete records in linked tables.
So, you need to code all the eliminations in VBA.
You should proceed step by step:
1) determine the list of tables that need to be cleaned up when you delete a field from the ETABLISSEMENT table
2) create a button in your form (the one you created is very good, but you will need to modify the linked VBA code).
To help you (VBA), I need more information.
- The ETABLISSEMENT table is linked to which other tables?
- By which fields?
Nothing is more dangerous than an idea when you only have one idea :-)
The delete button refuses to work because, it seems, it cannot delete records in linked tables.
So, you need to code all the eliminations in VBA.
You should proceed step by step:
1) determine the list of tables that need to be cleaned up when you delete a field from the ETABLISSEMENT table
2) create a button in your form (the one you created is very good, but you will need to modify the linked VBA code).
To help you (VBA), I need more information.
- The ETABLISSEMENT table is linked to which other tables?
- By which fields?
Nothing is more dangerous than an idea when you only have one idea :-)
Indeed, my delete button wouldn't work because of referential integrity, but by enabling the "delete cascade" button, it works.
To answer your question, the main table of my database is "employee". There are 4 tables linked to this first one: "establishment", "salary", "departure", and "job".
The "employee" table has "employee_id" as its primary key, and thus this field is a foreign key in my other tables. Each of my tables has an auto-generated number as the primary key.
The problem with cascade deletion is that I'm afraid if I delete an employee named Dupont in the "employee" table who was an industry operator, the delete button in the job table will delete all industry operators.
Could you enlighten me? Thank you, it helps me a lot.
To answer your question, the main table of my database is "employee". There are 4 tables linked to this first one: "establishment", "salary", "departure", and "job".
The "employee" table has "employee_id" as its primary key, and thus this field is a foreign key in my other tables. Each of my tables has an auto-generated number as the primary key.
The problem with cascade deletion is that I'm afraid if I delete an employee named Dupont in the "employee" table who was an industry operator, the delete button in the job table will delete all industry operators.
Could you enlighten me? Thank you, it helps me a lot.
The button will only delete records from the job table for which the primary key of your "employee" table is identical. Nothing else.
Nothing is more dangerous than an idea when you only have one idea :-)
Nothing is more dangerous than an idea when you only have one idea :-)
Hello,
Indeed, if you have a relationship between two tables and you check 'cascade delete', it means that for every record deleted, it will delete the related records in the other table. To change the issue, you can keep the link, but uncheck 'Enforce referential integrity'.
See you!
Indeed, if you have a relationship between two tables and you check 'cascade delete', it means that for every record deleted, it will delete the related records in the other table. To change the issue, you can keep the link, but uncheck 'Enforce referential integrity'.
See you!