MySQL+Access Tips 連結フォームで登録更新の同時処理

Insert と Update 命令を同時に実行する On Duplicate Key Update

MySQLデータベースのテーブルに Insert を行う方法は多数用意されているが、基本となるのはカラムを指定して対応する値を記述する方法。

INSERT テーブル名(カラム名,カラム名,・・・) VALUES(NUll,値,・・・) ;

対象となるカラムに自動採番(Auto Increment)が設定されているか、CURRENT_TIMESTAMP のような日時取得関数が設定されていれば NULL とする。もちろん値を記述しても構わない。

問題は Insert 時に主キーカラムに対して競合する値を記述した場合。
主キーはユニーク(一意)の値しか格納出来ないので当然エラー発生で処理が実行されないため、登録処理と訂正処理を分ける必要があることになる。しかし、出来れば 1 回で済ませたいだろう。例は簡単な商品マスタ管理画面。

実は、このフォームはテーブルに連結したフォーム。
おそらく一般的には外部データベースとのクライアントサーバシステムでは論外とされているはずなのだが、連結しているのは Access 内の作業テーブルである。フォームを開くときには簡単なコードで初期化される。

入力必須項目の一括設定

余技として、ユーザーに入力必須項目が分かりやすいように目立つ背景色にしている。
ここでは個別にコントロールの背景色を設定するのではなく、フォームを開くときイベントプロシージャで次のような簡単なコードで着色出来している。

    For Each CTL In Me.Form '-- フォームの全コントロールを調べる
        If CTL.Tag = "Not Null" Then
            CTL.BackColor = RGB(255, 255, 0)  '--- 特別なタグがあれば目立つ背景色にする
        End If
    Next

フォームデザインで対象とするコントロールを選択して、タグプロパティに任意の文字列を設定しておけば良い。

本来の目的は登録前の必須入力チェックだが、意外と簡単に目立たせることが出来るのが分かる。
入力規則プロパティでも良いのだがデータ型に影響されないタグプロパティの方が処理が簡単。
ユーザーが必要項目を入力して”登録”ボタンを押せば、登録済み商品リストが更新される。

商品リストの任意の行をクリックすると該当データを抽出して、フォームの RecordSource を入れ替えれることで選択したデータを表示出来る。

Private Sub 商品リスト_Click()
    '-- 選択された商品データを作業テーブルに読み込んでフォームに表示する
    FCD = Me.CD '-- 現在の商品CDを記録
    PQW = "PQ$-商品リスト"
    SQL = "Select * from 商品マスタ Where CD =" & Me.商品リスト.Column(0)
    Call PTクエリ作成(PQW, SQL)
    DB.Execute "Delete from " & WT
    SQL = "Insert into " & WT & " Select * from [" & PQW & "]" 'クエリオブジェクトは括弧が必要
    DB.Execute SQL
    Me.Requery
    Me.商品リスト = FCD '-- リストの選択状態を復元
End Sub

Accessの連結フォームで外部データベースの更新処理を行う

主キーが競合したら更新処理と判断

何と、MySQL には登録/ Insert と訂正/ Update を同時に実行してくれるありがたい機能がある。
とはいえデータベース・サーバーであればその機能が用意されていて当然ともいえる。

INSERT テーブル名(カラム名1,カラム名2,・・・) VALUES(値1,値2,・・・)
On Duplicate Key Update カラム名1=Values(カラム名1),カラム名1=Values(カラム名2),・・・ ;

1回のSQL文発行で Insert だけでなく、主キー競合時には更新目的とみなして Update を実行してくれる。
Update の後のカラム名は、競合時に更新すべきカラム名だけを記述すれば良い。つまりカラム数が一致している必要がないわけで、実に合理的。登録ボタンが押された時の処理は実質的に 1 行で済ませている。

Call マスタ登録("商品マスタ", "CD", Me)

標準モジュールに格納してあるマスタ登録処理を呼び出しているだけ。
マスタ登録プロシージャは次のようになっている。

Function マスタ登録(マスタテーブル As String, 主キー名 As String, 画面 As Form) As Boolean
    Dim ITEM As Variant, CNT As Integer, SQL2 As String
    Set DB2 = CurrentDb '-- 呼び出し元に影響しないオブジェクト名にする
    '-- マスタと一致する構造の作業用テーブルをクエリとして作成
    PQW = "PQ-TABLE"    '-- 保存済みクエリオブジェクトを流用
    DB2.QueryDefs(PQW).SQL = "Select * from " & マスタテーブル & " Where False"
    
    SQL = "Insert " & マスタテーブル & " Set "
    SQL2 = " On Duplicate Key Update "  '-- 同時更新を可能とするオプション句
    For Each CTL In DB2.OpenRecordset(PQW).Fields   '-- 作業用テーブルのフィールドリストを順次取得
        ITEM = 画面(CTL.Name).Value
        If CTL.Name = 主キー名 Then '-- 主キー定義されていたら NULL とする ※自動採番が前提
            SQL = SQL & CTL.Name & "=" & Nz(ITEM, "NULL")
        Else
            SQL = SQL & CTL.Name & "=" & IIf(IsNull(ITEM), "NULL", "'" & ITEM & "'")
        End If
        SQL2 = SQL2 & CTL.Name & "=Values(" & CTL.Name & "),"
                               '-- Values句により、更新時にカラム値の再利用が可能
        SQL = SQL & ","
        '-- 型判定をするには定数を利用(数値型=4 文字列型=10)
    Next
    SQL = Left(SQL, Len(SQL) - 1)
    SQL2 = Left(SQL2, Len(SQL2) - 1)
    DB2.QueryDefs("PQ-更新").SQL = SQL & SQL2
    DB2.Execute "PQ-更新"   '-- 更新クエリを実行
    Set DB2 = Nothi

連結フォームを使用する理由

上記のプロシージャでは、作業テーブルの全フィールドを取得してSQL文を組み立てている。
当然、外部データベースのテーブルと同じ構造である作業テーブルの使用が前提である。
マスタ管理フォームで連結フォームを使用している目的が、このSQL文自動生成ルーチン。
これさえあればほぼすべての登録処理が 1 行のコードで済む。制約としては主キー引数に注意することくらい。
これにより、フォーム毎に複雑なSQL文を地道に書き込むという不毛な作業から解放される。
テーブル構造に変更が生じた場合も作業テーブルの再作成だけで対応出来る。

On Duplicate Key Update を含む複雑なSQL文を自動生成

マスタ登録プロシージャで”PQ-更新”クエリとして生成されたSQL文は次のようになる。
フォーム毎に手作業でこんな複雑で難しいコードを書くことは考えられない。
型判定が面倒なのですべての値を文字列にしているが、MySQLは寛大なので問題なく受け入れてくれる。

Insert 商品マスタ Set CD=117,区分='1',品名='エメラルド・マウンテン',原価='650',単価='1200',掛率=NULL,適正在庫='5',仕入先ID='105',単位数='200',単位='g',備考=NULL,更新日時='2022/03/23 11:55:06' 
On Duplicate Key Update CD=Values(CD),区分=Values(区分),品名=Values(品名),原価=Values(原価),単価=Values(単価),掛率=Values(掛率),適正在庫=Values(適正在庫),仕入先ID=Values(仕入先ID),単位数=Values(単位数),単位=Values(単位),備考=Values(備考),更新日時=Values(更新日時)

このプロシージャでは、フォーム上のコントロールだけでなく作業テーブルに基づくすべてのフィールドを使って Insert しているが、Update の対象カラムを限定する機能まではない。※これ以上コードを複雑にしたくないため
もちろん更新対象カラムを指定すべき場面も少なからずあるので、その場合は必要に応じてフォーム内のクラスモジュールで同様のプロシージャを書けば良いだけである。
連結フォームの問題点は、フォームを表示するだけでもデータベースに負荷がかかることであり、リンクテーブルを使用している場合で更新が発生すると深刻な不具合のリスクが高まってしまう。
外部データベースと完全に切り離された作業テーブルベースの連結フォームなら全くその問題はない。
作業テーブルは手作業で作成しても良いが、自動生成プロシージャを作成しておけばラクになる。

Function 作業テーブル作成(DBマスタ名 As String) As Boolean
    Dim TD As TableDef, 作業テーブル名 As String
    PQ = "PQ-共通"
    Set DB2 = CurrentDb
    作業テーブル名 = "W_" & DBマスタ名
    For Each TD In DB2.TableDefs
        If TD.Name = 作業テーブル名 Then
            DB2.TableDefs.Delete 作業テーブル名 '-- 同名テーブルがあれば削除
            Exit For
        End If
    Next
    SQL = "Select * from " & DBマスタ名 & " Where False"    '-- データなしのテーブルを取得
    DB2.QueryDefs(PQ).SQL = SQL
    DB2.QueryDefs.Refresh
    ' 作業テーブル作成SQLは "Select * into テーブル名 from [オブジェクト名]"
    SQL = "Select * into " & 作業テーブル名 & " from [" & PQ & "]"
    DB2.Execute SQL
    作業テーブル作成 = True
    Set TD = Nothing
    Set DB2 = Nothing
End Function

データベースのテーブル名を指定すれば、”W_”を付加した空の作業テーブルが自動生成される。

主キーを調べるには

MySQLスキーマ内の隠し管理テーブルを調べればテーブル定義情報が取得できることが分かったのでメモしておく。
たまには英語表記にしてみようかと作った主キー名取得用関数。

Function PKEYGET(テーブル名 As String) As String
    'テーブルの主キー名取得 ※1個を前提
    Dim SQL As String
    SQL = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= '" & _
    テーブル名 & "' AND COLUMN_KEY='PRI'"
    PQW = "PQ$-主キー取得"
    Call PTクエリ作成(PQW, SQL)
    PKEYGET = Nz(DLookup("column_name", PQW), "未定義")
End Function

テーブルの主キーは基本的にひとつだから、マスタ登録プロシージャを更に簡単にすることも出来るな。
デバッグウィンドウでの実行結果。

?PKEYGET(“受注マスタ”)
受注番号