LinuxのデータベースサーバーにWindows10からODBC接続する

2022年3月9日2022年11月27日

ODBCアドミニストレータが使えない !

現在取り組んでいる案件では、遠隔地の事業所とのオンライン接続が必要。
規模の大小があっても製造業なら珍しくもなんともない形態だろう。とにかくサーバーはLinuxであってもクライアントは既存のPC流用なのですべてWindows機であり必然的にODBC接続となる。

Windows上のクライアントアプリから外部データベースであるMySQLに接続するにはODBCの仕組みが必要。
ところがいつの間にか、Windows10のコントロールパネルから「ODBCアドミニストレータ」が表示されなくなっていた。
長い間利用していなかったので、10の当初から廃止されていたのか分からないが、MicroSoft が普及させた規格なのでなくなることはないはず。
もちろん検索機能で調べると見つかった。しかし・・・

新規作成画面に入ると何と SQL Server ドライバーしか出てこないのだった。
当然マイPCにMySQL用ドライバーはインストールしている。
嫌がらせとしか思えないが、Excel や Access からであれば作成出来ることが分かった。

A1 Style

[MySQL] ExcelからデータベースにODBCで接続する方法 | A1 Style MySQLのデータベースに、ExcelからODBCで接続する方法を紹介します。シートに取得したデータは、ブックを開くたび最新の状態にすることも、スナップショットとして保存し…

AccessからODBC接続用DSNファイルを作成するには

Access メニューではこんな感じ。ここからウィザードで進むと無事に制約なしの作成画面が表示される。
続く画面で注意するのは”リンクテーブルを作成”とし、データソースの選択画面で”新規作成”とすること。

ようやく通常のODBCアドミニストレータメニューになった。
親画面のキャプションがなぜか (32ビット) になっているが手抜きなだけだろう。
ここから問題なくDSNファイルを作成出来る。
詳しくはリンク先をどうぞ。

A1 Style

[MySQL] AccessからデータベースにODBCでリンクする方法 | A1 Style MySQLのデータベースに、AccessからODBC接続でリンクする方法です。データベースにリンクしたテーブルを、Accessに作成します。クエリやVBAによる処理が可能になるので、…

ちなみに、プログラム本体は C:\Windows\SysWOW64\odbcad32.exe のようだ。
ウィザードで指定したフォルダーに作成されたDSNファイルの内容は単なるテキストのみ。

[ODBC]
DRIVER=MySQL ODBC 8.0 Unicode Driver
UID=MYSQLユーザー名
DFLT_BIGINT_BIND_STR=1
PORT=3306
DATABASE=データソース名
SERVER=サーバーIPアドレス

前記の手順でリンクテーブルを作成すると、MySQLのパスワード入力後に無事にテーブルが表示出来た。
リンクテーブルのデザイン画面ではなぜか説明プロパティとしてODBC接続文字列が保存されている。
生成されたリンクテーブルの接続文字列は次のようになっている。変更は不可。

ODBC;DRIVER=MySQL ODBC 8.0 Unicode Driver;PORT=3306;DATABASE=データベース名;SERVER=サーバーIPアドレス;FILEDSN=DSNファイルパス.dsn;;TABLE=テーブル名

DSNファイルの各項目をセミコロンでつないでテーブル名を追加しているだけ。ドライバー名の中カッコは要らん気がする。ちなみに、当然といえば当然だが、Accessデータベースを開き直してリンクテーブルを開こうとするときっちりパスワード入力画面が表示されるので実用にならない。
その回避策はあるのだが、DSNファイル作成目的はリンクテーブル生成ではなく、ODBC接続文字列の確認である。
接続文字列をコピーしてしまえば、その後は用なしなのでリンクテーブルはさっさと削除してしまおう。

AccessからのODBC接続はパルスルークエリに限る

クエリ新規作成で種類を”パルスルー”とし、DSN生成のODBC接続文字列を少し加工する。
といっても余分な項目を削除してPWD項目を追加するだけで準備完了である。

ODBC;DRIVER=MySQL ODBC 8.0 Unicode Driver;UID=MySQLユーザー名;PWD=パスワード;PORT=3306;DATABASE=データベース名;SERVER=サーバーIPアドレス;

DSNファイルは不要

上記の接続文字列から分かるが、DSNファイル名の項目などなくてもパルスルークエリもリンクテーブルも問題なく機能する。つまりクライアントPCにDSNファイルを配布する必要はないし、その存在は百害あって一利なしなので当然。
ODBCドライバーのバージョン統一こそ最重要。違ってるとアプリそのものを開けなくなるから。
つまり開発PCと、実際にアプリが運用されるクライアントPCで同じバージョンのODBCドライバーをインストールすることが重要であるが、現実的にはIT分野の業種でなければ小規模事業所で専任の情報システム担当責任者が存在することは例外に等しいのでシステムを納品するものが納入アプリと同時にODBCドライバーのインストールも行うはずなので単なるメモ。

パルスルークエリはSQLウィンドウで簡単なSQLコマンドを記述すれば同じ結果が得られる。
とりあえず結果が確認出来れば適当な名前でクエリオブジェクトを保存しておく。
システム内のパルスルークエリは基本的にVBAコードで生成と破棄を繰り返すため保存は必須ではないが、いくつかコピーしておけば開発中は何かと便利なのである。
パルスルークエリの場合、Workbechと同じく直接MySQLデータベースにアクセスすることになるので高速で高性能であり安定性も万全である。
その代償として、記述するSQL文はMySQLの仕様に合わせなければならないし、ビュー(テーブル結合)作成ではグラフィカルで便利なクエリデザインも使えない。※他の有償ツールでは可能

Accessのリンクテーブルを使わない理由

外部データベース構築では、次の致命的問題によってリンクテーブルは一切使えないし使わない。

Accessのデータベースエンジンを経由するため、通信状態が悪いと少量のデータでもレスポンスが極端に悪化するためサーバーにも余計な負荷が発生してしまう
安易にコードを書くと全レコードを取得するためサーバーに膨大な負荷がかかる
accde(実行専用)ファイルであっても簡単な操作でテーブル操作(データ改ざん)可能になる危険性がある

つまりAccessの役割はフロントエンドアプリとしてだけである。
便利なクエリデザイン機能も使用不可だしデータベース機能もほぼ使わない。
それではAccessなんか使う意味がないといえばそうなのだが、自分が慣れているので開発効率が良いのだ。
とはいえ、すべてのデータ処理をパルスルーで行わねばならず、クリアすべき問題が山積である。
一応順調に進んでいるはいるが、すぐに忘れてしまうので記事作成は自分のためでもある。