AccessのクエリをSQLに直すと、何割がそのまま動きますか?見本95本と書き方14種の対応表

AccessのクエリをSQLに直すと、何割がそのまま動きますか?

見本の95本では、56%の53本がそのまま通りました。機械置換を足すと75本(79%)、手で直した5本を足すと80本(84%)になり、残る15本は作り直しです。見本はMicrosoftが配るNorthwind 2.0のStarter版(クエリ30本)とDeveloper版(65本)で、移し先はPostgreSQL 17.6です。

ここでの「通る」は、保存クエリをビューとして作れて、結果がAccessと同じことです。テーブルの全行(Starter版233行、Developer版611行)をPostgreSQLに入れ、Accessで動かした結果と行の順を無視して突き合わせました。

見本は業務用ではないため、社内のクエリより素直な書き方に偏っている可能性があります。割合は目安です。

機械置換で直るのは、どの書き方ですか?

見本で直した書き方の大半は、角括弧の外しです。機械置換で通った22本のうち18本が角括弧を使っていて、次に多いのが日付の関数(5本)と&連結(5本)でした。Format・DateValue・Space・Date()・Year()の5つが、置換した関数です。

Accessの書き方14種を、PostgreSQLの書き方と並べました。「見本」は95本のうち、その書き方が出た本数です。最後の列は、Accessの書き方のまま1行の試験でPostgreSQLに渡した結果です。

Iif・Like・クロス集計は見本に1本も出ませんでした。この3つは、表のとおり1行の試験だけで確かめています。クロス集計は列が固定なら、FILTER付きの集計に書き直せます。

通ったのに結果が違うのは、どんな書き方ですか?

構文が通っても結果が違ったのは、NULLとワイルドカードの扱いが違う4つです。&を||に置換すると、空の値が入った行でAccessは「様」、PostgreSQLはNULLを返しました。Accessは空の値を空の文字として足します。concatはNULLを無視します(PostgreSQL 17の文書)。

Like ’A*’はエラーにならず、PostgreSQLでは0行でした。Accessの*と?は、PostgreSQLでは%と_に読み替えます。

見本の中では、結合先の氏名とPlainTextで差が出ました。左外部結合で相手がいない行で、Accessは半角の空白を返し、PostgreSQLはNULLでした。PlainText(空のメモ)も、Accessは空の文字、PostgreSQLはNULLです。どちらもCOALESCEで包んで直しました。

&の置換でも、qryCustomerListの連絡先の氏名がNULLになり、concatに替えました。

手で直す、作り直すのは、どんなクエリですか?

手で直した5本は、PlainText(2本)、結合先の空白(2本)、Accessの側で壊れていた1本です。壊れていた1本は、Developer版のqrySales_SalesRepです。Employeesテーブルにない列FullNameFNLNを指していて、Accessで開くと「パラメーターが少なすぎます」と出ました。Accessは見つからない名前を、入力を求めるパラメーターとして扱います。

作り直した15本は、フォームの値を条件にする9本と、VBAの自作関数を呼ぶ6本です。フォームの値は、引数つきの関数にして呼ぶ側から渡します。VBAの自作関数は7個あり、Accessの外では動きません。PostgreSQLの側で関数を書き直す必要があり、この15本は結果の突き合わせをしていません(作成が通るかだけを見ました)。

これらは、VBAの分類を扱ったAccessのVBAを移すときの7つの種類と同じ考え方で、作り直す範囲に入ります。型の違いはAccessのデータ型の変換表にあります。

自分のAccessで同じ数え方をするには?

保存クエリのSQLを書き出し、PostgreSQLに通して、段階ごとに数えます。手順は5つです。

1. 全員が閉じた時間にAccessの写しを取り、QueryDefsからSQLを書き出す(access_query_dump.ps1)
2. テーブルの列と全行を書き出し、PostgreSQLに空のテーブルとデータを作る
3. Accessで各クエリを動かした結果を書き出す
4. SQLの文字をそのまま渡し、通らなければ機械置換、次に手直しを試す
5. 結果を行の順を無視して突き合わせ、段階ごとの本数を数える

要点は次の4行です(実物はaccess_query_sql.pyのmechanical関数)。

sql = sql.replace("&", "||")                          # 連結(NULL で結果が変わるので手直し対象)
sql = re.sub(r"\[([A-Za-z_]\w*)\]", r"\1", sql)      # [角括弧] を外す
sql = re.sub(r"\bDate\s*\(\s*\)", "CURRENT_DATE", sql)
sql = re.sub(r"\bYear\((.*?)\)", r"EXTRACT(YEAR FROM \1)::int", sql)

スクリプトはaccess_query_sql.pyで、14種の1行試験はaccess_sql_probes.pyです。

限界もあります。見本はStarter版9テーブル・Developer版28テーブルの小さなデータで、3本は結果が0行同士の一致でした。Format(‘ww’)はPostgreSQLのWWが年の第1日から数える週(文書)なので、Accessと同じかは別に確かめます。

数える入口は、Accessの棚卸しと作った人がいないAccessの中身を数える手順にあります。移す全体の流れはAccessの移行のやり方にまとめています。

このサイトで相談するには?

クエリが何本あり、どれが作り直しになるかは、棚卸しで見当がつきます。何をどう頼むか決まっていなくても大丈夫です。御社のやり方に合わせて、進め方を一緒に考えます。

準備は要りません。無料で相談するからお送りください。