Accessプログラム よくある？トラブル集（Excel連携編）

1. 実行時エラー：-2147217900 SQLステートメントが正しくありません

（現象）
ExcelVBAからADO経由でAccessのクエリーを実行したときに、たまに発生するエラーです。
「実行時エラー：-2147217900 SQLステートメントが正しくありません。　DELETE INSERT PROCEDURE SELECT または UPDATEを使用してください。」

Dim cmd As ADODB.Command
Dim rs1 As New ADODB.Recordset

Set con = New ADODB.Connection
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB 12:Data Source=C:\test.accdb"
con.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = con
With cmd
    .CommandText = "qIs_サービス別件数・工数集計"
    Set rs1 = Execute()            ' ⇒ここでエラーになる

（原因）
・指定したクエリー名 or SQLの文法が間違っている。
・Nz関数など、Accessでしか使えない（⇒ADOでは使えない）関数を使っている。
意外と落とし穴ですが、ADOを介しているため、次のようにNzなどAccess固有の関数は使えません。

qIs_サービス別件数・工数集計

INSERT INTO tWk_集計ワーク
SELECT 'サービス別件数' As 式1,
Count([作業No]) As 作業Noのカウント,
Nz(Sum([実績時間]), 0) As 実績時間の合計    -- ⇒ここがダメ
FROM tTr_年度作業実績;

（対策）
・挿入処理の後で、結果対象カラムがNullであれば0に置き換える更新処理を実行。
　上の例の場合、いったん「実績時間の合計」カラムをNullのままレコード挿入する。
　その後、「実績時間の合計」ががNullであれば0に置き換えるSQL or 更新クエリーを実行する。

---

2. 文字列の包含検索で、指定した条件にうまくヒットしない

（現象）
ExcelVBAからADO経由で実行した文字列の包含検索で、指定した条件にうまくヒットしない。

SELECT 作業No, 件名, 実績時間
FROM tTr_年度作業実績
WHERE 件名 LIKE "*DNS*"        -- １件もヒットしない・・・

（原因）
これも同様の原因で、Access固有のワイルドカード指定"*"は、ADOを介すると半角アスタリスクの文字列とみなされるため、意図した検索結果が返ってきません。
https://learn.microsoft.com/ja-jp/dotnet/framework/data/adonet/ef/language-reference/like-entity-sql

（対応）
ワイルドカードに"%"を指定したSQL or 選択クエリーを実行します（SQL Server等と同じ）。

SELECT 作業No, 件名, 実績時間
FROM tTr_年度作業実績
WHERE 件名 LIKE "%DNS%";

とはいえ、（過去の経緯はあるとはいえ）せめてこれくらいは、Microsoft内で融通利かせてほしいものですよね・・・