Zenn
🚀

VBA ADO - ODBC接続 MySQLエラー対策、必須のDNSオプション

2024/02/28に公開
MySQL
VBA
odbc
tech

ADOの更新処理が不安定になる

状況：
VBA ADO ODBC接続でMySQLをRecoredset編集(Edit,Update,Insert,Delete)

結果：
SQL構文は問題ないのにエラーが発生する

エラーメッセージ例：

  • 更新する行がみつからなかったため、クエリーベースの更新に失敗しました
  • 複数ステップの OLE DB の操作でエラーが発生しました。各 OLE DB の状態の値を確認してください。作業は終了しませんでした
  • -2147467259 更新に必要なキー列の情報が足りません。

実行後のエラー例

  • 改行のあるLONGTEXTが1行のみ反映される

原因１ MySQLとMicrosoftの仕様違い

MySQLの更新処理系のフィールドサイズ、行数カウントがMicrosoft社製品(ASP,VB,Access,Excelなど)と異なるため結果が不整合になるから。

原因１ の対策

ODBC接続時のDNSオプションを追加する。
OPTION = 3 の場合、 1+2 という意味。

-　　DB.Open "DRIVER={MySQL};SERVER=192.168.99.99;USER=usr;PASSWORD=pwd;DATABASE=dbname;"
+　　DB.Open "DRIVER={MySQL};SERVER=192.168.99.99;USER=usr;PASSWORD=pwd;DATABASE=dbname;OPTION=3;"

OPTION=1; Don't optimize column width

取得したカラムのサイズを最適化しないを有効。

OPTION=2; Return matching rows

クエリー実行後の処理結果行数を「クエリに一致した行数」で返すを有効にする。

OPTION=2が無効時には、Microsoft製品で発行したクエリの処理行数とODBC接続されたMySQLでの更新結果行数に齟齬が起き、それをMicrosoft製品では処理失敗と見なす場合がある。

LONGLONG型(16bit)整数を正常に扱う事が出来ない

MDAC 2.6SP1以前は、ODBC Driver ManagerがLONGLONG型(16bit)整数を正常に扱う事が出来ないためBIGINT型集計が狂うような不具合もあった（らしい、未検証）。

その場合、下記オプションを有効にする必要があった。

OPTION = 16384
# Change LONGLONG columns to INT columns(some applications can't handle LONGLONG)

原因2 文字化け

MySQL側はUTF-8の時、ODBCをUnicodeにすることで検索結果は正常に取得できる。
しかし、更新処理（VBAクライアントからMySQLサーバへの更新リクエスト）では改行コードなどが文字化けを起こす。

この場合、DNS接続時にsjisを指定することで解決する。

-　　DB.Open "DRIVER={MySQL};SERVER=192.168.99.99;USER=usr;PASSWORD=pwd;DATABASE=dbname;"
+　　DB.Open "DRIVER={MySQL};SERVER=192.168.99.99;USER=usr;PASSWORD=pwd;DATABASE=dbname;Charset=sjis;"

原因3 そもそも不可能

-2147467259 更新に必要なキー列の情報が足りません。

JOINを使った中間テーブルにRecordsetでの更新はできない

Discussion