Search code examples
sqlvb.netlinqms-access

How to delete master-detail in MS Access in one SQL database in VB.NET?

I'm trying to delete master-detail in MS Access in one SQL database in VB.NET.

If it can't be done using SQL in MS-Access, can it be done using Linq or something else using VB.NET?

Please guide me.

Thanks

I tried this SQL select command, it worked:

SELECT *
FROM purchase 
INNER JOIN purchasedetail ON purchase.invono = purchasedetail.invono
WHERE (((purchasedetail.invono) = "PI-0001"));

But when I tried the SQL delete command, it didn't work:

DELETE *
FROM purchase 
INNER JOIN purchasedetail ON purchase.invono = purchasedetail.invono
WHERE (((purchasedetail.invono) = "PI-0001"));

1

2

This is my VB.NET code:

Public Class PurchaseService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.accdb;Persist Security Info=False;"
    End Function

    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()

    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub

    'So I don't want to use 2 action methods but 1 action method

    Public Sub DeletePurchase(ByVal Obj As purchase)
        Dim sql = $"Delete  * FROM purchase WHERE Invono = '{Obj.Invono}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub

    Public Sub Deletepurchasedetail(ByVal Obj As purchase)
        Dim sql = $"Delete  * FROM purchasedetail WHERE Invono = '{Obj.Invono}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class

Public Class purchase
    Public Property Invono() As String
    Public Property dateInvono() As Date
End Class

Public Class purchasedetail
    Public Property Invono() As String
    Public Property dateInvono() As Date
    Public Property No() As Integer
    Public Property CodeProduct() As String
    Public Property Qty() As Integer
End Class
Solution

  • The simplest solution is to setup an enforced relationship with cascade delete. That way from the forms (UI), or from SQL commands, or from code, the child records will automatic be deleted for you. No joins etc. are required. This of course means that deleting of any parent record will automatically delete the child records.

    Hence all you need is this:

    DELETE * FROM Purchase WHERE Purchase.ID = 1234

    In other words, you should be deleting the parent record by using a PK value. You can of course use other columns in this parent table for the delete, but in MOST cases the delete should be done and specified using the PK column value.

    If you do NOT have enforced relationships (with cascade delete), then you of course will have to execute 2 delete commands, and in near all cases, the steps are to delete the child records FIRST.

    And if you have more then 2 child tables, then again, you have to execute a delete command for each child table, and THEN delete the one main parent table record.

    Once again, this means and suggests that you REALLY, but REALLY but REALLY REALLY REALLY REALLY need to have the primary key value of the ONE parent record.

    So, you need to have the parent record primary key.

    You then would to delete the one parent record, and the child records to do the following for each child table:

     -- first get the primary key value of the main record.

    You then delete the child records first AND DO SO BASED ON THE primary key value of the parent record. Since as noted, the standard approach is to first delete the child records, then of course the FK (foreign key) value is to be used for the deletes in the child table (since some other columns will not in general suffice, since OTHER child records in that child table might have the same values.

    So, you will need a separate delete command for each child table, and then you delete the on main parent record in the parent table. I should point out that even when using SQL server or most database systems, the above approach is how this is to work.

    So, you need to delete child records first. You don't mention what the parent record PK (Primary key) value column is, and you don't mention what the FK (foreign key) column is in the child table. Assuming (and a wild guess on my part), then we have this:

    ' Remove Child records first
DELETE * FROM purchasedetail where invono = "PI-0001"

' Remove Parent record
DELETE * FROM purchase WHERE invono = "PI-0001"

    Of course, it not clear what columns are actually used for the PK and FK, but the above is the general approach.

    However, if there is a real PK/FK set of columns then the value from the parent PK value should be used for both the child records delete, and then deleting of the one parent record.

    As noted, if you enforce the relationship and ALSO set cascade delete?

    Well, then you can't add child records without first having a parent record. This is nice, since then your database will never have so called orphan records (child records without a parent record can’t exist in the database).

    And the big bonus of course is that you ONLY have to delete the ONE parent record, and all child records are automatic deleted for you.

    But, at the end of the day? In most cases, and in fact in most database systems, you are required to execute delete commands for each child table, and then are to execute a delete command against the one main parent record.