如何在VB.NET中用单条SQL删除MS Access主从表数据？

一、Access SQL联表删除的限制 #

Access的SQL不支持通过单条DELETE语句同时删除主表和从表的关联数据，你之前的联表DELETE语句失败，核心原因是Access不允许这种跨多表的批量删除语法。要实现原子性删除，必须将两个删除操作放在同一个事务中执行，确保要么都成功，要么都回滚。

二、VB.NET单方法事务实现 #

你可以将两个删除操作整合到一个方法里，通过OleDb事务保证操作的原子性，避免分两次调用独立方法：

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

    Public Sub DeletePurchaseWithDetail(ByVal Obj As purchase)
        Using conn = New OleDbConnection(GetOledbConnectionString())
            conn.Open()
            ' 开启事务
            Using trans = conn.BeginTransaction()
                Try
                    ' 先删从表数据（外键关联必须先删从表，否则会触发约束错误）
                    Dim deleteDetailSql = "DELETE FROM purchasedetail WHERE Invono = ?"
                    Using cmd = New OleDbCommand(deleteDetailSql, conn, trans)
                        cmd.Parameters.Add(New OleDbParameter("@Invono", Obj.Invono))
                        cmd.ExecuteNonQuery()
                    End Using

                    ' 再删主表数据
                    Dim deletePurchaseSql = "DELETE FROM purchase WHERE Invono = ?"
                    Using cmd = New OleDbCommand(deletePurchaseSql, conn, trans)
                        cmd.Parameters.Add(New OleDbParameter("@Invono", Obj.Invono))
                        cmd.ExecuteNonQuery()
                    End Using

                    ' 提交事务
                    trans.Commit()
                Catch ex As Exception
                    ' 出错回滚事务
                    trans.Rollback()
                    Throw ex ' 可根据业务需求自定义异常处理逻辑
                End Try
            End Using
        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

关键注意点：

• 必须先删从表再删主表，否则Access的外键约束会直接阻止主表的删除操作。
• 使用参数化查询替代字符串拼接，彻底避免SQL注入风险。
• 事务机制保证两个操作的原子性，任何一步执行失败都会回滚所有已执行的操作。

三、Linq方式实现（基于Entity Framework） #

如果使用Entity Framework（比如EF Core搭配Access驱动），可以通过上下文事务实现单方法删除：

首先安装Access的EF Core驱动（如Microsoft.EntityFrameworkCore.Access），然后定义DbContext：

Public Class AppDbContext
    Inherits DbContext
    Public Property Purchases As DbSet(Of purchase)
    Public Property PurchaseDetails As DbSet(Of purchasedetail)

    Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)
        optionsBuilder.UseAccess(GetOledbConnectionString())
    End Sub

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

然后实现删除方法：

Public Sub DeletePurchaseWithLinq(ByVal invono As String)
    Using context = New AppDbContext()
        Using trans = context.Database.BeginTransaction()
            Try
                ' 删除对应从表数据
                Dim details = context.PurchaseDetails.Where(Function(d) d.Invono = invono)
                context.PurchaseDetails.RemoveRange(details)

                ' 删除主表数据
                Dim purchase = context.Purchases.FirstOrDefault(Function(p) p.Invono = invono)
                If purchase IsNot Nothing Then
                    context.Purchases.Remove(purchase)
                End If

                ' 保存更改并提交事务
                context.SaveChanges()
                trans.Commit()
            Catch ex As Exception
                trans.Rollback()
                Throw ex
            End Try
        End Using
    End Using
End Sub

四、Access单条SQL删除的替代方案（仅单表） #

如果一定要用单条SQL，只能单独删除单表数据，无法同时操作两个表。比如先执行：

DELETE FROM purchasedetail WHERE Invono = 'PI-0001';

再执行：

DELETE FROM purchase WHERE Invono = 'PI-0001';

但必须将这两条语句放在同一个事务中执行，才能保证数据一致性，本质和前面的事务实现逻辑一致。

内容的提问来源于stack exchange，提问作者user22579796