MySQLのデータをAccessデータシートとして表示する万能フォーム

2022年3月15日2022年3月18日

Workbench 最大の不満点のひとつを解消

テーブルを作成したり挿入・更新したデータを即座に見やすく表示してくれるのはMySQL Workbenchのメリットのひとつだが、欠点もある。

数値データもすべて左寄せ表示になってしまうのである。コマンドラインツールの実行結果ですべて文字列扱いになるのはやむを得ないだろうが、せっかくのGUIツールがこれでは気持ちが悪い。

すべてのテーブルとビューをブラウズ出来るツール

そんなわけで、Accessパススルークエリの演習ついでにユーティリティツールを作成してみた。

データシートスタイルで格段に見やすくなり、一気にアプリケーションらしくなったと自画自賛。
単純に表示するだけで更新機能などないので実用にならないが「万能」と称しているのは、このフォームひとつでデータベース内のテーブルとビューをすべて扱えるという機能にある。簡素だが検索機能も付けてある。
開発とメンテ用に作ってみたら想定外にうまく出来たので、つまづいた点を忘れないために残しておく。

テーブルとビューをコンボボックスで選択

ここでのテーブルというのはもちろんMySQLデータベースのテーブルである。
カンタンなSQLコマンドで取得出来るので、手始めとしてはちょうど良い。
コンボボックスの”値集合ソース”プロパティをテーブル/クエリとし、フォームのOpenイベントに次のように書く。

Private Sub Form_Open(Cancel As Integer)
    Limit = 1: 抽出数 = 200 '-- 取得開始行と表示行数の設定
    Set DB = CurrentDb
    PQW = "PQ$-01"  '-- 使い捨てパススルークエリ名
    Call PTクエリ作成(PQW)  '-- 標準モジュールのカスタム関数を利用
    DB.QueryDefs(PQW).SQL = "Show Tables;"  '-- テーブル名取得SQLコマンド
    Me.対象テーブル.RowSource = PQW '-- コンボボックスのリスト更新
End Sub

Show Tables;

という、MySQLのSQLコマンドをパススルークエリオブジェクトの”SQL”プロパティに代入することでテーブル名とビュー名のリストを取得出来る。
ところで。本シリーズの目的は「VBAコーディング入門」などと大それたものではない。つまり、「各種変数やDBオブジェクト型変数はフォームのクラスモジュール宣言部に記述してある」・・・というような超基本的な事柄は最低限にしておく。
エラー処理などを省いていて、必要最低限のコードで済ませているが、それにしても良くこれで動くものだと自分でも感心しきりである。
ここでのポイントは、Accessの初期関門となる問題の解決方法である。

コンボボックスやリストボックスの値集合ソースをパススルークエリで管理

いくら簡単に出来るといっても、リモートDBに対してリンクテーブルの使用は論外なのですべてのデータ処理を徹底的にパススルークエリのみで行う、という目論見も無謀かもしれないがとにかく出来るところまでやってみよう。
Accessによる業務システム構築で最初に直面する重要課題のひとつがコンボボックスの扱い
フォームのコントロールに必要な数だけパススルークエリオブジェクトを作成するという愚直な方法でも実現は可能だが、たとえ小規模であっても”業務システム”を開発しようというのに、まさかそんな非効率な方法は通用しない。

補助的なパススルークエリは自動生成して使い捨てとする

ネット上にも多数の例がある定番のような解決方法が”パススルークエリの自動生成と破棄”である。

Function PTクエリ作成(クエリ名 As String) As Boolean
    Dim QD As QueryDef
    Set DB = CurrentDb
    On Error GoTo エラー
    For Each QD In DB.QueryDefs '-- 同名クエリが存在していたら削除する
        If QD.Name = クエリ名 Then
            DB.QueryDefs.Delete クエリ名
        End If
    Next
    Set QD = DB.CreateQueryDef  '-- クエリオブジェクトの内部生成
    With QD
        .Name = クエリ名
        .SQL = "Show Tables;"  '-- SQLプロパティは必須だが適当な内容で良い
        .Connect = CN1  '-- グローバル変数のODBC接続文字列を代入
    End With
    DB.QueryDefs.Append QD  '-- クエリオブジェクトを保存
    DB.QueryDefs.Refresh    '-- オブジェクトリストを更新
    PTクエリ作成 = True     '-- 正常終了すれば True を返す
終了処理:
    Set QD = Nothing: Set DB = Nothing  '-- オブジェクト変数を解放
    Exit Function
エラー:
    MsgBox Error, vbCritical, Err   '-- 問題が発生したらエラー内容を表示
    Resume 終了処理     '-- エラー対応後に終了処理まで戻る
End Function

少し長くなってしまったのは、エラー処理用のためのコードが増えているため。
エラー発生の可能性はほぼないのだが多用する重要なカスタム関数なのでエラー処理を入れてある。
イベントプロシージャから Call PTクエリ作成(クエリ名) と書けばクエリオブジェクトがデータベースオブジェクトとして保存される。関数として作成しているので、呼び出しには Call 命令を書かないと動かない。
注意点としては、同一モジュール内で重複しない特殊な名称にすることだけ。
本システムでは、後処理を容易にするため使い捨て用クエリ名は先頭3文字を”PQ$“で統一している。
CN1 というのは、ODBC接続用に標準モジュールで宣言してあるグローバル定数である。
システム全体で普遍であり一貫した情報となるので Const 命令で定数としている。
内容はというと、DSNファイルで生成された情報項目をセミコロンで区切っただけ。

Public Const CN1 = “ODBC;DRIVER=MySQL ODBC 8.0 Unicode Driver;UID=paradox;PWD=○○○○;PORT=3306;DATABASE=○○○○;” & _
“SERVER=192.168.○.○;”

言うまでもなく、データベースを複数使い分ける場合は必要な数だけ定数を作れば良いし、頻度が多くなければSQL文を正式表記で書けばひとつの定数でも対応出来る。
いずれにしても、使い捨てクエリにAccessのクエリデザイン機能は全く必要ない。
CreateQueryDef メソッドでクエリを生成して、Connect プロパティに定数を代入してやるだけでパススルークエリが出来上がる。ちなみに、Workbench では SELECT * FROM 商品マスタ; と書いても実行する時に

SELECT * FROM sample.商品マスタ;

と勝手にデータベース修飾子を付加してくれる。

プロシージャを徹底的に短くするには

ちなみに、バグは発生しないまたは対応が容易に完了することを前提に「手抜きしても良いじゃないか」主義としては、次のような極限的に短く書いたコードでも問題なく動作するので常用している。

Function PTクエリ作成手抜き版(クエリ名 As String) As Boolean
    On Error Resume Next
    Dim QD As QueryDef
    Set DB = CurrentDb
    Set QD = DB.CreateQueryDef  '-- クエリオブジェクトの内部生成
    With QD
        .Name = クエリ名
        .SQL = "Show Tables ;"  '-- SQLプロパティは必須だが適当な内容で良い
        .Connect = CN1  '-- グローバル変数のODBC接続文字列を代入
    End With
    DB.QueryDefs.Append QD  '-- クエリオブジェクトを保存
    DB.QueryDefs.Refresh    '-- オブジェクトリストを更新
    PTクエリ作成手抜き版 = True     '-- 正常終了すれば True を返す
    Set QD = Nothing: Set DB = Nothing  '-- オブジェクト変数を解放
End Function

デバッグウインドウでテスト実行してみると、ちゃんとAccessデータベースのオブジェクトリストに”PQ$-00″が追加されているはず。

?PTクエリ作成手抜き版(“PQ$-00”)
True

魔法の呪文 On Error Resume Next により、同名オブジェクトが存在していようがお構いなしにパススルークエリオブジェクトを生成してくれる。問答無用で結果も True になる。

使い捨てクエリオブジェクトを一括削除

フォームを閉じたら用済みになる使い捨てクエリは、標準モジュール内に作成してある。
手抜きに便利な Optional 句により、引数を省略してもデフォルト設定した”PQ$”が採用されようになる。
フォーム閉じる時などに適宜 Call 作業クエリ削除 と書けば一括処理が出来る。

Function 作業クエリ削除(Optional 先頭文字 = "PQ$") As Boolean
    On Error GoTo エラー
    Dim QD As QueryDef
    Set DB = CurrentDb
    For Each QD In DB.QueryDefs
        If Left(QD.Name, 3) = "PQ$" Then
            Debug.Print "削除 : " & QD.Name
            DB.QueryDefs.Delete QD.Name
        End If
    Next
    作業クエリ削除 = True
終了処理:
    Set QD = Nothing: Set DB = Nothing
    Exit Function
エラー:
    MsgBox Error, vbCritical, Err
    Resume 終了処理
End Function

前項同様、クエリ削除も省略版ではこうなる。

Function 作業クエリ削除手抜き版(Optional 先頭文字 = "PQ$") As Boolean
    On Error Resume Next
    Dim QD As QueryDef
    Set DB = CurrentDb
    For Each QD In DB.QueryDefs
        If Left(QD.Name, 3) = "PQ$" Then
            DB.QueryDefs.Delete QD.Name
        End If
    Next
    作業クエリ削除 = True
    Set QD = Nothing: Set DB = Nothing
End Function

実行結果を得る必要がなければ、Call で呼び出せば良い。

Call 作業クエリ削除手抜き版

アプリケーションシステムを終了させる時に一括削除

本システムではいちいち面倒くさいので、業務終了後の”システム終了ボタン”操作時に動作するようにしている。

Private Sub システム終了_Click()
    Call 作業クエリ削除
    MSG = "すべての画面を閉じて業務を終了しますか ?"
    If MsgBox(MSG, vbCritical + vbYesNo + vbDefaultButton2, "操作確認") <> vbYes Then Exit Sub
    Application.Quit    '-- Accessアプリケーションウィンドウ自体を閉じる
End Sub

記事が長くなったので、続きは次号にて。

よかったらシェアしてね！