AccessにDELETEするまとめ

Option Explicit

Sub DELETE_Access()
'AccessにDELETEするまとめ
'MicsosoftActive X Data Objects Library 6.1がearly bindingしてあるものとして

'------Access-----------------
Dim AccessConnection As String
Dim AccessFileFullPath As String
AccessFileFullPath = Environ("UserProfile") & "¥Documents¥SampleData.accdb"

AccessConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & AccessFileFullPath & ";" & _
                                "Persist Security Info=False;"

Dim adoConn As ADODB.Connection
Dim adoRecSet As ADODB.Recordset

Set adoConn = New ADODB.Connection
'Set adoRecSet = New ADODB.Recordset

Dim AccessOutputSheet As Worksheet
Set AccessOutputSheet = ThisWorkbook.Worksheets("AccessOutput")


'------Accessファイルを開く-----------------
  adoConn.Open AccessConnection 'Accessファイルを開く"
    Debug.Print "Access Connection Opened"

'結果の出力先をクリアーしておく
AccessOutputSheet.Range("A19", Range("A19").End(xlDown).End(xlToRight)).Clear

'------AccessデータにSELECTで接続する-----------------
Dim AccessSQLString As String
With AccessOutputSheet
    AccessSQLString = "DELETE FROM 販売管理 " & _
                        " WHERE [社員名] = " & "'" & .Range("L15").Value & "'" & ";"
End With

Debug.Print AccessSQLString
Stop
On Error GoTo ErrorHandler

    adoConn.BeginTrans
    adoConn.Execute AccessSQLString

    adoConn.CommitTrans

'------Accessコネクションを閉じる-----------------
adoConn.Close
    Debug.Print "Access Connection Closed"

Set adoConn = Nothing

Exit Sub
ErrorHandler:
    Debug.Print "IN ERROR"
    adoConn.RollbackTrans
    adoConn.Close
    Set adoConn = Nothing
End Sub