データベースなどではトランザクションという、データの整合性を図ろうとする仕組みがあるようです。
操作前にセーブしておいて、何かが起きたら操作を破棄してセーブしていたところに戻す、というようなイメージです。
トランザクションは単純な動作に向いているといわれています。「補完的な業務領域」用の考え方のようです。
とはいえ、Accessだけを利用している際には使ったことがなかったので試しにMariaDBとAccessの組み合わせのデータベースで使ってみることにしました。
CurrentProject.Connectionではエラーになる
まず、試したのが、「CurrentProject.Connection.BeginTrans」メソッドです。こんなメソッドあったんですね。
しかしながら、どうしてもエラーになります。
でたエラーは、
「-2147217900 (80040E14) Execute の後にクエリ名が必要です。」
というものでした。ただ、テーブルへの書き込みは実行されてしまっていることが多かったです。
エラーを防ぐためのトランザクションを使うとエラーになるという何とも皮肉な展開です。
よく、デバックしてみるとどうやらAccess側からは上記のエラーになる前には別のエラーが起きていたようです。
それは
「-2147168242 (8004D00E)| コミットまたはロールバックを実行するには、BeginTrans メソッドを使用してください。」
あら！そもそもトランザクション始まってない!!
どうやらCurrentProject.Connectionオブジェクトではよくなかったのかもしれません。
MariaDBを操作することはできても、Access側としては操作していないと解釈されているのかなと想像しています。
別のやり方として、ADODB.Connectionを明示してやってみることにしました。
ADODB.ConnectionからMariaDBを操作
ADODBから操作する際に手こずったのが、単純な接続文字列の記載ミスや、VBAの記載ミスでした。
「ODBC Driver Manager データ ソース名および指定された既定のドライバーが見つかりません。」
というものでした。どうやらどこか記述が間違っていたようでした。
いきなりデータベースを操作せず、接続だけ試してみたところうまくいきました。
Sub TestMariaDBConnection()
Dim conn As Object
Dim connectionString As String
connectionString = "Driver={MariaDB ODBC 3.2 Driver};" & _
"Server=localhost;" & _
"Database=testdb;" & _
"UID=pccol;" & _
"PWD=ikebukuro;" & _
"Option=0;"
On Error GoTo ErrorHandler
Set conn = CreateObject("ADODB.Connection")
conn.Open connectionString
If conn.State = 1 Then
MsgBox "接続に成功しました！", vbInformation
Else
MsgBox "接続に失敗しました。", vbExclamation
End If
conn.Close
Set conn = Nothing
Exit Sub
ErrorHandler:
MsgBox "エラーが発生しました：" & vbCrLf & Err.Description, vbCritical
If Not conn Is Nothing Then
If conn.State = 1 Then conn.Close
Set conn = Nothing
End If
End Sub
フォームで入力後ADODBでINSERTする
かなり苦戦しましたが、何とかフォームからテーブルへデータを入力することができました。
入力はうまくいくが、最後のプロシージャが終了する際にフリーズすることが多かったので、Cleanupセクションをいろいろ工夫してみました。
フォームで入力後、コマンドボタンをクリックするとこちらのスクリプトを実行するようなフォームにしました。
Public Sub InsertProductADODB(ByVal tableName, _ ByVal pCode As String, _ ByVal pName As String, _ ByVal pPrice As Currency, _ ByRef logText As String) Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim recs As Long Dim txnStarted As Boolean txnStarted = False Dim connectionString As String On Error GoTo ErrHandler Set conn = New ADODB.Connection connectionString = "Driver={MariaDB ODBC 3.2 Driver};" & _ "Server=localhost;" & _ "Database=testdb;" & _ "UID=pccol;" & _ "PWD=ikebukuro;" & _ "Option=0;" conn.Open connectionString conn.BeginTrans txnStarted = True Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandText = "INSERT INTO " & tableName & _ "(product_code, product_name, unit_price) " & _ "VALUES ('" & pCode & "', '" & pName & "', " & pPrice & ")" cmd.Execute recs conn.CommitTrans logText = "登録成功：" & pCode & "（" & recs & "件）" & vbCrLf Cleanup: On Error Resume Next ' コマンドオブジェクトの明示的切断と破棄 If Not cmd Is Nothing Then cmd.Cancel ' 念のためキャンセル Set cmd.ActiveConnection = Nothing Set cmd = Nothing End If 'トランザクションを確認 If conn.State = 1 Then If conn.Errors.Count = 0 Then conn.CommitTrans Else conn.RollbackTrans End If End If ' 接続オブジェクトの状態確認と破棄 If Not conn Is Nothing Then If conn.State = 1 Then conn.Close ' adStateOpen = 1 を直接指定 DoEvents Set conn = Nothing End If Exit Sub ErrHandler: ' エラー情報記録（ログ出力の中心） logText = "予期しないエラー：" & _ "番号：" & Err.Number & "（" & Hex(Err.Number) & "）" & vbCrLf & _ "内容：" & Err.Description & vbCrLf Debug.Print logText ' トランザクション開始済みなら Rollback を試みる 'トランザクションを確認 If conn.State = 1 Then If conn.Errors.Count = 0 Then conn.CommitTrans Else conn.RollbackTrans logText = logText & "→ Rollbackを実行しました。" & vbCrLf txnStarted = False End If End If Resume CleanupEnd Sub
