Excel VBA での Transaction について

環境はWin10 Pro x64 + Excel2013 x32 + SQL Server 2016 Express x64

使用したデータはこちら郵便番号検索の17ISHIKA.CSV。

使用したテーブルはこちら

元ネタはこちら

1.BeginTrans、CommitTrans、RollbackTrans メソッド

2.ObjectStateEnum

1.より抜粋

ネストされたトランザクションをサポートしているプロバイダの場合、開いているトランザクションで BeginTrans メソッドを呼び出すと、ネストされた新規トランザクションが開始されます。戻り値は、ネストのレベルを示します。戻り値 "1" はトップ レベルのトランザクション (ほかのトランザクション内でネストしていないトランザクション)、"2" はセカンド レベルのトランザクション (トップ レベルのトランザクション内でネストしているトランザクション) が開いたことを示します ("3" 以下も同様です)。CommitTrans または RollbackTrans メソッドを呼び出すと、最後に開いたトランザクションのみを操作します。さらに上のレベルのトランザクションを処理するには、カレント トランザクションを閉じるか、またはロール バックする必要があります。

2.より抜粋

ObjectStateEnum

定数	値	説明
adStateClosed	0	オブジェクトが閉じていることを示します。
adStateOpen	1	オブジェクトが開いていることを示します。
adStateConnecting	2	オブジェクトが接続していることを示します。
adStateExecuting	4	オブジェクトがコマンドを実行中であることを示します。
adStateFetching	8	オブジェクトの行が取得されていることを示します。

ADO/WFC 等価

パッケージ: com.ms.wfc.data

定数
AdoEnums.ObjectState.CLOSED
AdoEnums.ObjectState.OPEN
AdoEnums.ObjectState.CONNECTING
AdoEnums.ObjectState.EXECUTING
AdoEnums.ObjectState.FETCHING

テーブル
CREATE TABLE [dbo].[POSTAL](
    [POSTAL]   [nchar](8) NULL,
    [PREF]     [nchar](20) NULL,
    [CITY]     [nchar](40) NULL,
    [ADRLINE1] [nchar](40) NULL,
    [ADRLINE2] [nchar](40) NULL,
    [ADRLINE4] [nchar](40) NULL
) ON [PRIMARY]

Private Function setCSV2DB() As Boolean

On Error GoTo ERR_PROC

    Dim adoCon      As ADODB.Connection
    Dim adoRst      As ADODB.Recordset
    Dim vAry        As Variant
    Dim host        As String
    Dim user        As String
    Dim pass        As String
    Dim conn        As String
    Dim strSQL      As String
    Dim fd          As Integer
    Dim strLine     As String
    Dim fName       As String
    Dim trLevel     As Long

    host = "SQLSVODBC32"
    user = "demo"
    pass = "demo"
    fName = "C:\Dev\code\vscode\excel\17ISHIKA.CSV"
    fd = FreeFile

    conn = "DSN=" & host & ";UID=" & user & ";PWD=" & pass
    Open fName For Input As #fd

    Set adoCon = New ADODB.Connection
    adoCon.ConnectionString = conn
    adoCon.CursorLocation = adUseClient
    
Debug.Print adoCon.State         '2. より 0
    
    Call adoCon.Open
    trLevel = 0
Debug.Print adoCon.State         '2. より 1

    strSQL = "TRUNCATE TABLE POSTAL"
    trLevel = adoCon.BeginTrans  '1. よりトップ レベルのトランザクション = 1
Debug.Print trLevel

    Call adoCon.Execute(strSQL, 0)

    While Not EOF(fd)
        Line Input #fd, strLine

        strLine = Replace(strLine, """", "")
        vAry = Split(strLine, ",")
        strSQL = "INSERT INTO POSTAL (POSTAL, CITY, ADRLINE1) VALUES "
        strSQL = strSQL & "('" & Trim(vAry(2)) & "','" & Trim(vAry(7)) & "','" & Trim(vAry(8)) & "')"
        Call adoCon.Execute(strSQL, 0)
    Wend

    adoCon.CommitTrans
    trLevel = trLevel - 1
Debug.Print adoCon.State

    adoCon.Close
    Set adoCon = Nothing
    Close #fd
    dbSetCSV = True
    Exit Function

ERR_PROC:
    trLevel = trLevel - 1
    adoCon.Close
    Set adoCon = Nothing
    Close #fd
    dbSetCSV = False

End Function