リンクテーブルではダメなのか

2006.1 ACCESS 2000 SP-3 + PostgreSQL 8.0.4

リンクテーブルはMDBファイルのテーブルと同様にODBCデータソースのテーブルを扱うことのできる大変便利な機能です。これによりACCESSは、異種データソースの結合という優れた機能を実現しています。この機能を使えば、PostgreSQLに移行したテーブルをODBC経由のリンクテーブルとして置き換えるだけでACCESSで作った既存アプリケーションはそこそこに動いてしまいます。

しかし、リンクテーブルをローカルHDDのMDBファイルにあるテーブルをスタンドアロンで使う場合のように扱うと、ネットワークの向こうにあり、複数ユーザーで利用するデータベースならではの問題にぶつかることになります。また、ACCESSとPostgreSQLの機能の違いにより、ACCESSがクエリを処理してしまい、PostgreSQLがデータベースとしての機能を発揮せず、ただのデータファイルのようになってしまうクエリがあります。

前準備

PgAdminⅢなどを用いて、PostgreSQLデータベースにテスト用データベース（test1）をUNICODEで作成します。
「ODBCデータソースアドミニストレータ」でテスト用データベース接続用のシステムDSNを作成します。
ACCESSからODBCドライバを経由してどのようなデータが流れているかを見るためにODBCドライバの一般ログ出力をONにします。
サンプルテーブル生成ツールをダウンロードし、テストテーブル作成ボタンを押してサンプルテーブルやクエリを作成します。
リストボックス内のクエリを順次開いては閉じます。

ログを見る

作成したクエリを順次実行した後、ACCESSを一旦終了して、c:\にできているpsqlodbc_xxxx.logファイルをエディタやメモ帳などで開きます。ログファイルの末尾にクエリ実行時のACCESSとPostgreSQL通信内容が出力されているはずです。

ログファイルの先頭部にはいろいろとSQL文には見えない情報が出力されていますが、ODBC DriverやACCESSが接続時に行う各種設定および情報取得処理です。中にはいくつかエラーが出ていると思いますが、ACCESSの画面でクエリの結果が表示できているならさほど問題ありません。ERRORの文字が気になる人は、"TryJetAuth"や"MsysConf"でネットを検索してください。

ACCESSからODBC経由のデータベースを利用する際の注意点は、ここに記述されています。

ACCESS関数を含むクエリ

PostgreSQLでサポートされないACCESS（JET）関数を含むクエリは、ACCESS（JET）が処理します。
集計クエリでよく使う「先頭（First）」もPostgreSQLにはない関数なので、列の意味に応じてGROUP BYに入れるかmin関数への置き換えが必要です。

「q_日別商品分類別売上」のSQL文

SELECT u.売上日, msb.商品分類名, Sum(u.売上数) AS 売上数計, Sum([売上数]*[単価]) AS 売上額
FROM (t_売上 AS u INNER JOIN t_商品台帳 AS ms ON u.商品cd = ms.商品cd) INNER JOIN t_商品分類台帳 AS msb ON ms.商品分類cd = msb.商品分類cd
GROUP BY u.売上日, msb.商品分類名
ORDER BY u.売上日, msb.商品分類名;

「q_日別商品分類別売上」の実行文

conn=206387064, query='
SELECT "u"."売上日" ,"msb"."商品分類名" ,SUM("u"."売上数" ) ,SUM(("u"."売上数" * "u"."単価" ) )
FROM "public"."t_商品分類台帳" "msb","public"."t_商品台帳" "ms","public"."t_売上" "u"
WHERE (("ms"."商品分類cd" = "msb"."商品分類cd" ) AND ("u"."商品cd" = "ms"."商品cd" ) )
GROUP BY "u"."売上日" ,"msb"."商品分類名"
ORDER BY "u"."売上日" ,"msb"."商品分類名" '
[ fetched 13 rows ]

こちらのクエリは、PostgreSQLで集計（Sum関数）が実行されている。しかし、PostgreSQLに存在しないLeft関数を使う次のクエリではPostgreSQLで集計が行われない。

「q_商品名3桁別売上」のSQL文

SELECT Left([商品名],3) AS 商品名先頭3文字, Sum(u.売上数) AS 売上数計, Sum([売上数]*[単価]) AS 売上額
FROM t_売上 AS u INNER JOIN t_商品台帳 AS ms ON u.商品cd = ms.商品cd
GROUP BY Left([商品名],3)
ORDER BY Left([商品名],3);

「q_商品名3桁別売上」の実行文

conn=206387064, query='
SELECT "u"."売上数" ,"u"."単価" ,"ms"."商品名"
FROM "public"."t_商品台帳" "ms","public"."t_売上" "u"
WHERE ("u"."商品cd" = "ms"."商品cd" ) '
[ fetched 710 rows ]

SQL文からSumが消えている。PostgreSQLで集計されずに、集計対象レコードをすべて取得してACCESSが集計を行う。

サブクエリ

なんというか、ACCESS（JET）が苦心して実行します。

「q_商品別売上sub」「q_商品別売上」のSQL文

SELECT t_売上.商品cd, Sum(t_売上.売上数) AS 売上数計
FROM t_売上
GROUP BY t_売上.商品cd;

SELECT ms.商品名, sub.売上数計
FROM t_商品台帳 AS ms INNER JOIN q_商品別売上sub AS sub ON ms.商品cd = sub.商品cd
ORDER BY ms.商品名;

「q_商品別売上」の実行文

conn=206387064, query='SELECT "商品cd" ,SUM("売上数" ) FROM "public"."t_売上" GROUP BY "商品cd" '
[ fetched 705 rows ]
conn=206387064, query='SELECT "商品cd" ,"商品名" FROM "public"."t_商品台帳" "ms" WHERE ("商品cd" = 317)'
[ fetched 1 rows ]
conn=206387064, query='SELECT "商品cd" ,"商品名" FROM "public"."t_商品台帳" "ms" WHERE ("商品cd" = 316)'
[ fetched 1 rows ]
...以下延々と705行分、商品cd条件を変えたクエリが続く。

クロス集計クエリ

PostgreSQLには存在しない機能なので、必要なデータをPostgreSQLからすべ取得してACCESSで実行されます。

「q_日別商品別売上」のSQL文

TRANSFORM Sum(t_売上.売上数) AS 売上数計
SELECT t_売上.売上日
FROM (t_売上 INNER JOIN t_商品台帳 ON t_売上.商品cd = t_商品台帳.商品cd) INNER JOIN t_商品分類台帳 ON t_商品台帳.商品分類cd = t_商品分類台帳.商品分類cd
GROUP BY t_売上.売上日
ORDER BY t_売上.売上日
PIVOT t_商品分類台帳.商品分類名;

「q_日別商品別売上」の実行文

conn=206387064, query='
SELECT "public"."t_売上"."売上日" ,"public"."t_売上"."売上数" ,"public"."t_商品分類台帳"."商品分類名"
FROM "public"."t_商品分類台帳","public"."t_商品台帳","public"."t_売上"
WHERE (("public"."t_商品台帳"."商品分類cd" = "public"."t_商品分類台帳"."商品分類cd" ) AND ("public"."t_売上"."商品cd" = "public"."t_商品台帳"."商品cd" ) ) '
[ fetched 710 rows ]

外部結合

クエリの内容次第で、PostgreSQLで処理されるかACCESSで処理するかが変わります。
外部結合が複数連なるクエリはACCESSが処理の一部を担うようです。
サブクエリと外部結合を含むクエリの場合は、面白い動きをします。INNER JOINできるものをわざと外部結合にした方が効率が良い場合があります。↓はその例です。

サブクエリと外部結合を含むクエリ

q_外部結合sub

SELECT t_売上.商品cd, Sum(t_売上.売上数) AS 売上数計, Sum([売上数]*[単価]) AS 売上額
FROM t_売上
WHERE (((t_売上.売上日) Between #12/1/2005# And #12/5/2005#))
GROUP BY t_売上.商品cd;

q_外部結合

SELECT t_商品台帳.商品cd, t_商品台帳.商品名, q_外部結合sub.売上数計, q_外部結合sub.売上額
FROM t_商品台帳 LEFT JOIN q_外部結合sub ON t_商品台帳.商品cd = q_外部結合sub.商品cd
WHERE (((q_外部結合sub.売上数計) Is Not Null))
ORDER BY t_商品台帳.商品cd;

サブクエリと外部結合を含むクエリの実行文

conn=174471032, query='SELECT "商品cd" ,SUM("売上数" ) ,SUM(("売上数" * "単価" ) ) FROM "public"."t_売上" WHERE ("売上日" BETWEEN '2005-12-01'::date AND '2005-12-05'::date ) GROUP BY "商品cd" '
[ fetched 355 rows ]
conn=174471032, query='SELECT "商品cd" ,"商品名" FROM "public"."t_商品台帳" '
[ fetched 706 rows ]

2回の問い合わせでデータを取得し、後はACCESSが外部結合します。これを下記のように書き換えると

q_外部結合を内部結合に書き換えたもの

SELECT t_商品台帳.商品cd, t_商品台帳.商品名, q_外部結合sub.売上数計, q_外部結合sub.売上額
FROM t_商品台帳 INNER JOIN q_外部結合sub ON t_商品台帳.商品cd = q_外部結合sub.商品cd
ORDER BY t_商品台帳.商品cd;

内部結合に書き換えたクエリの実行文

conn=196622200, query='SELECT "商品cd" ,SUM("売上数" ) ,SUM(("売上数" * "単価" ) ) FROM "public"."t_売上" WHERE ("売上日" BETWEEN '2005-12-01'::date AND '2005-12-05'::date ) GROUP BY "商品cd" '
[ fetched 355 rows ]
conn=196622200, query='SELECT "商品cd" ,"商品名" FROM "public"."t_商品台帳" WHERE ("商品cd" = 511)'
[ fetched 1 rows ]
conn=196622200, query='SELECT "商品cd" ,"商品名" FROM "public"."t_商品台帳" WHERE ("商品cd" = 510)'
[ fetched 1 rows ]
...以下延々と355行分、商品cd条件を変えたクエリが続く。

更新可能なダイナセット型クエリ

クエリ「q_商品台帳」は、不可解なSQL文に分割実行されます。

「q_商品台帳」のSQL文

SELECT t_商品台帳.*, t_商品分類台帳.商品分類名
FROM t_商品台帳 INNER JOIN t_商品分類台帳 ON t_商品台帳.商品分類cd = t_商品分類台帳.商品分類cd
ORDER BY t_商品台帳.商品cd;

「q_商品台帳」の実行文

conn=205535096, query='
SELECT "public"."t_商品台帳"."商品cd","public"."t_商品分類台帳"."商品分類cd"
FROM "public"."t_商品分類台帳","public"."t_商品台帳"
WHERE ("public"."t_商品台帳"."商品分類cd" = "public"."t_商品分類台帳"."商品分類cd" )
ORDER BY "public"."t_商品台帳"."商品cd" '
[ fetched 706 rows ]
conn=205535096, query='
SELECT "商品分類cd","商品分類名"
FROM "public"."t_商品分類台帳"
WHERE "商品分類cd" = 0 OR "商品分類cd" = 1 OR "商品分類cd" = 1 OR "商品分類cd" = 1 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2'
[ fetched 3 rows ]
conn=205535096, query='
SELECT "商品cd","商品名","商品分類cd"
FROM "public"."t_商品台帳"
WHERE "商品cd" = 0 OR "商品cd" = 1 OR "商品cd" = 2 OR "商品cd" = 3 OR "商品cd" = 5 OR "商品cd" = 6 OR "商品cd" = 101 OR "商品cd" = 102 OR "商品cd" = 103 OR "商品cd" = 104'
[ fetched 10 rows ]
conn=205535096, query='
SELECT "商品分類cd","商品分類名"
FROM "public"."t_商品分類台帳"
WHERE "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2 OR "商品分類cd" = 2'
[ fetched 1 rows ]
conn=205535096, query='
SELECT "商品cd","商品名","商品分類cd"
FROM "public"."t_商品台帳"
WHERE "商品cd" = 105 OR "商品cd" = 106 OR "商品cd" = 107 OR "商品cd" = 108 OR "商品cd" = 109 OR "商品cd" = 110 OR "商品cd" = 111 OR "商品cd" = 112 OR "商品cd" = 113 OR "商品cd" = 114'
[ fetched 10 rows ]

更新可能なダイナセットとして開いたq_商品台帳クエリは、使用テーブルの主キーの組み合わせである商品cdと商品分類cdを取り出すSQL文と、商品cdを個別に10個づつWHERE句で指定した表示列を取り出すSQL文およびその10個の行の商品分類名を取得するSQL文に分割されて実行されています。ACCESSのクエリ結果画面で上下に数画面分スクロールしてからログを見ると、表示データを取得するためにACCESSが裏側で実行しているSQL文の動きがよくわかります。

ここで、一旦ログファイルを閉じて、ACCESSを再度起動し、q_商品台帳クエリのプロパティ画面で「レコードセット」プロパティを「ダイナセット」から更新不能な「スナップショット」に変更して実行します。ACCESSを終了して、ログファイルを再度開くと、その末尾に、「スナップショット」型で実行した「q_商品台帳」の実行結果が出力されています。

「q_商品台帳」スナップショット型での実行文

conn=194983800, query='
SELECT "public"."t_商品台帳"."商品cd" ,"public"."t_商品台帳"."商品名" ,"public"."t_商品台帳"."商品分類cd" ,"public"."t_商品分類台帳"."商品分類名"
FROM "public"."t_商品分類台帳","public"."t_商品台帳"
WHERE ("public"."t_商品台帳"."商品分類cd" = "public"."t_商品分類台帳"."商品分類cd" )
ORDER BY "public"."t_商品台帳"."商品cd" '
[ fetched 706 rows ]

今度は素直に1つのSQL文で全列を取得しています。元がWHERE句で絞り込んでいない"ダメダメ"クエリなので、706 rowsもレコードを取得してしまっていますが、画面表示や帳票出力に最低限必要なデータだけを取得するように適切な条件をつければ実務上は問題ありません。

「q_商品台帳」で更新を行った場合の実行文

conn=194983800, query='SELECT "商品分類cd","商品分類名" FROM "public"."t_商品分類台帳" WHERE "商品分類cd" = 2'
[ fetched 1 rows ]
conn=194983800, query='SELECT "商品cd","商品名","商品分類cd" FROM "public"."t_商品台帳" WHERE "商品cd" = 101'
[ fetched 1 rows ]
conn=194983800, query='SELECT "商品分類cd","商品分類名" FROM "public"."t_商品分類台帳" WHERE "商品分類cd" = 1'
[ fetched 1 rows ]
conn=194983800, query=
UPDATE "public"."t_商品台帳"
SET "商品分類cd"=1
WHERE "商品cd" = 101 AND "商品名" = 'D101' AND "商品分類cd" = 2'
conn=194983800, query='COMMIT'

クエリ結果画面で、商品cd=101の商品分類cdを1に変更した際に実行されたSQL文です。ACCESSは、UDATE文にたどり着く前に3個のSELECT文を発行しています。（SELECT文は更新時ではなく、画面で行編集中に実行されています。）
UPDATE文は、WHERE句に全列が指定されたものになります。これは他者によるレコードの変更を検出するのに役立ちますが、効率の悪いものです。（ここの情報によれば、SQL Serverでいうところのtimestamp列が存在するとACCESSはそれを利用して変更チェックを行うようです）

バージョン列表示オプションONの状態で「q_商品台帳」で更新を行った場合の実行文

psqlODBC Driverでは、「バージョン列表示」（RowVersioning）オプションをONにすることで、xminシステム列にSQL Serverのtimestamp列の役割を果たさせることができるようです。

conn=198770760, query='SELECT "商品分類cd","商品分類名","xmin" FROM "public"."t_商品分類台帳" WHERE "商品分類cd" = 2'
[ fetched 1 rows ]
conn=198770760, query='SELECT "商品cd","商品名","商品分類cd","xmin" FROM "public"."t_商品台帳" WHERE "商品cd" = 101'
[ fetched 1 rows ]
conn=198770760, query='SELECT "商品分類cd","商品分類名","xmin" FROM "public"."t_商品分類台帳" WHERE "商品分類cd" = 1'
[ fetched 1 rows ]
conn=198770760, query='
UPDATE "public"."t_商品台帳"
SET "商品分類cd"=1
WHERE "商品cd" = 101 AND "xmin" = 58151'
conn=198770760, query='
SELECT "商品cd","商品名","商品分類cd","xmin"
FROM "public"."t_商品台帳"
WHERE "商品cd" = 101'
[ fetched 1 rows ]
conn=198770760, query='COMMIT'

UPDATE文のWHERE句はすっきりしますが、SELECTデータにxminが増えるのと、更新後の最新状態取得用のSELECT文が増えます。

まるでローカルテーブルであるかのようにVBAで更新する

商品台帳をDAO Recordsetで更新するVBAコード

Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("t_商品台帳")
rs.FindFirst "商品cd=102"
If Not rs.NoMatch Then
　rs.Edit
　rs![商品分類cd] = 1
　rs.Update
End If

実行されたSQL文

conn=198588280, query='SELECT "public"."t_商品台帳"."商品cd" FROM "public"."t_商品台帳" '
[ fetched 706 rows ]
conn=198588280, query='SELECT "public"."t_商品台帳"."商品cd" FROM "public"."t_商品台帳" WHERE "商品cd" = 102'
[ fetched 1 rows ]
conn=198588280, query='SELECT "商品cd","商品名","商品分類cd" FROM "public"."t_商品台帳" WHERE "商品cd" = 102'
[ fetched 1 rows ]
conn=198588280, query='SELECT "商品cd","商品名","商品分類cd" FROM "public"."t_商品台帳" WHERE "商品cd" = 102'
[ fetched 1 rows ]
conn=198588280, query='
UPDATE "public"."t_商品台帳"
SET "商品分類cd"=1
WHERE "商品cd" = 102 AND "商品名" = 'D102' AND "商品分類cd" = 1'
conn=198588280, query='COMMIT'

商品台帳テーブルを無条件にOpenRecordsetで開くと、全件706rowsの主キーを取得してしまっています。テーブルをOpenRecordsetし、FindFirstで処理対象行に移動して更新を行うという処理は、移行に際して書き換えが必要です。

TOPに戻る　MS ACCESSからPostgreSQLへ移行するに戻る