2009年4月16日木曜日

Access の VBA でトランザクション処理 – CSV ファイルからデータの登録

トランザクションと言えば、やはりこの本。

未だ買ってすらいない。 パタッ(o_ _)o~†

昔からデータベースのこの辺がチンプンカンプン。入門レベルを読んでもトランザクション、排他制御になるとついていけない。いつかはちゃんと頭の中を整理したいのだけれど… (+_+)

 

クライアントから見たトランザクション

トランザクションの大枠については以下の図を参照。

異常なければコミット、異常があればロールバック。これだけ聞くとシンプルなのだけれど…。とりあえず、クライアントアプリがトランザクションを利用するときのテンプレートを書いておくことに。参考にしたのは以下の二つの記事。

Access の ADO については、「ActiveX Data Object : ADO入門講座」を。

 

テンプレート

以下、ADO でテーブルにレコードを追加するときのテンプレート。Java のように try ~ catch がないのでエラーハンドラと Resume を利用。

ポイントは、トランザクションが開始された後に異常が置きたらロールバック。正常異常を問わず、最後にデータベースへのコネクションを閉じる。ただし、その際コネクションが存在していることを確認すること。

Sub tmplADOTran()
On Error GoTo Err_Handler
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim blnTran As Boolean
    
    blnTran = False
    
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    ' トランザクションの開始
    cn.BeginTrans                   '
    blnTran = True

    rs.Open "テーブル名", cn, adOpenKeyset, adLockOptimistic
    
    ' データを列ごとに追加する
    rs.addnew
    rs!列名 = "値"
    rs.Update
    
    ' コミット
    cn.CommitTrans
    blnTran = False
    
Finally:
    If Not rs Is Nothing Then rs.Close
    If Not rs Is Nothing Then cn.Close
    
    Exit Sub
    
Err_Handler:
    ' ロールバック
    If blnTran Then cn.RollbackTrans
    MsgBox "エラーが発生しました: " & Err.Description, vbOKOnly
    
    Resume Finally
End Sub

あぁ~、何かややこしいなぁ。 (+_+)

 

例. CSV ファイルのデータを登録

上記のテンプレートを元に、CSV ファイル (test.csv) のデータをテーブルに登録するコードを書いてみる。ただし、対象のテーブルは一つ。追加する先のテーブルの列は文字列型とする。

まず、データを追加するテーブルの名称と列の名前を保持する Table クラスを作成。(挿入 > クラスモジュール) 値を保持するだけなのでインスタンス変数は Public に。

Public name As String               ' テーブルの名称
Public cols As New Collection       ' 列の名前のコレクション

上記のテーブル情報を持ったオブジェクトと、データ元となるファイルへのパスを渡すとレコードを追加する関数は以下のようになる。

Sub insert(objTbl As table, strFilePath As String)
On Error GoTo Err_Handler
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim blnTran As Boolean
    
    Dim fileNo As Integer
    Dim idx As Integer
    
    blnTran = False
    
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    ' トランザクションの開始
    cn.BeginTrans                   '
    blnTran = True

    rs.Open objTbl.name, cn, adOpenKeyset, adLockOptimistic
    
    ' CSV ファイルからデータを一行ずつ読み込む
    fileNo = FreeFile()
    Open strFilePath For Input As #fileNo
    Do Until EOF(fileNo)
        Line Input #fileNo, strLine
        strAry = Split(strLine, ",")
        
        ' データを列ごとに追加する
        rs.addnew
        
        idx = 0
        For Each col In objTbl.cols
            rs(col) = strAry(idx)
            idx = idx + 1
        Next
        
        rs.Update
    Loop
    Close #intFileNo
    
    ' コミット
    cn.CommitTrans
    blnTran = False
    
Finally:
    If Not rs Is Nothing Then rs.Close
    If Not rs Is Nothing Then cn.Close
    
    Exit Sub
    
Err_Handler:
    ' ロールバック
    If blnTran Then cn.RollbackTrans
    MsgBox "エラーが発生しました: " & Err.Description, vbOKOnly
    
    Resume Finally
End Sub

上記を例えば次のように呼ぶ。

Sub test()
    Dim objTbl As New table
    Dim filePath As String
    
    ' CSV ファイルのパス
    filePath = Application.CurrentProject.Path & "\test.csv"
    
    objTbl.name = "テーブル名"        ' データを追加するテーブルの名前
    objTbl.cols.Add "列名"      ' データを追加するテーブルの列の名前
    
    insert objTbl, filePath
End Sub

 

参考