0

I have a Data Table in Excel and is directly linked to an Access DB. When you right click on the Data Table then there are an option to delete rows. But i would like to delete the records in the Access datasource as well when i delete rwos on the datatable. How can i achieve this?

Community
  • 1
  • 1
Jason Samuels
  • 951
  • 6
  • 22
  • 40

1 Answers1

0

Capture the event Worksheet_BeforeRightClick on the sheet you wish to enable this feature, write some code to delete the record within the Access database, refresh the linked table on the sheet.

The code to delete a record from an Access database can be found here: Deleting data from Access with VBA

In order to set the where restriction correctly (to only delete the record you want to) you'll need to know in which column the primary key is on the sheet and then you can grab that value for the where restriction as simple as:

Worksheets("Sheet1").Cells(Target.Row, PK_Column).Value2
Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42
  • I am not sure about this method. What if I rightclick, the record gets deleted and i decide otherwise. – Jason Samuels Jun 25 '15 at 09:45
  • @jason-samuels: Then you can set a message box asking to confirm, if they would really like to delete the record. Furthermore, you can do it similar to Google and only say that you delete it without actually deleting it. Just add a flag to the Access table with a bit called IsDeleted and if that one is set then you don't show that record. The same happens with Google Emails. Just because you don't see them anymore doesn't mean that Google actually deleted your emails. ;) – Ralph Jun 25 '15 at 09:53