Accessプログラム　よくある？トラブル集（単体編） #VBA

Accessで作成したプログラムを保守運用していると、ときにそこそこキャリアのある筆者でもあたふたするほどのトラブルに見舞われることがあります。
ただまあ、ほぼ98%以上は人間（⇒主に筆者）が原因だったりするんですが。

ここでは、筆者が過去経験したAccessプログラムのトラブルとその対応策をお知らせし、同じような現象に悩まれている方の一助になればと。

ボリューム的に多くなったので、単体編／Excel連携編の２部に分けています

1. DoCmd.RunSQLでトランザクションが効かない

Microsoftのドキュメントには、Docmd.RunSQLコマンドの第二引数にTrueを指定するとトランザクションに含められる云々と明記されているので、あえて明言は避けますが・・・
おそらく、DoCmd.RunSQLコマンドにトランザクションは効きません。

Public Sub トランザクションテスト()

    ' DAOの例
    Dim ws1 As Workspace
    Set ws1 = DBEngine.Workspaces(0)
    ws1.BeginTrans
        ' いずれもRollbackが効かずに挿入されてしまう
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO 都道府県 VALUES ('鹿児島県')", False  ' トランザクションに含めない
        DoCmd.RunSQL "INSERT INTO 都道府県 VALUES ('沖縄県')", True     ' トランザクションに含める
        DoCmd.SetWarnings True
    ws1.Rollback
    
    ws1.BeginTrans
        ' この２つへのRollbackは効く
        CurrentDb.Execute "INSERT INTO 都道府県 VALUES ('愛知県');"
        
        With CurrentDb.QueryDefs("都道府県データ挿入クエリー")
            .Parameters("p_都道府県") = "岐阜県"
            .Execute
            .Close
        End With
    ws1.Rollback
    
    
    ' ADOの例
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    
    Set cn = CurrentProject.Connection
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "BEGIN TRANSACTION"
        .Execute
        
        ' この例でも、いずれもRollbackが効かずにINSERTされてしまう
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO 都道府県 VALUES ('青森県')", False    ' トランザクションに含めない
        DoCmd.RunSQL "INSERT INTO 都道府県 VALUES ('山形県')", True     ' トランザクションに含める
        .CommandText = "ROLLBACK TRANSACTION"
        .Execute
        DoCmd.SetWarnings True
    End With
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing
    
End Sub

筆者の場合、（好みの問題もありますが）もっぱらパラメータクエリーを定義してQueryDef.Executeで実行するようにしたら、トランザクションの問題は起こらなくなりました。

2. データ操作時の原因不明のエラー

クエリー実行時やデータソースと紐づいたフォームの操作時に、突然
「引数が無効です」
のエラーメッセージが表示されて、データベースの修復を実行しても直らないことがあります。

これも「たぶん」で恐縮ですが、おそらくフォームやテーブルなどの情報を内部的に管理しているテーブル（システムテーブル）が壊れたのが原因と思われます。
この場合は、新しく作った空のaccdbファイルに元ファイルの全要素をインポートし、各種オプションを設定しなおしてそちらを使うようにします。
（エラーとなった元ファイルは、念のためしばらく残した後に削除します）

3. RecordsAffectedプロパティーの使い方

AccessのDatabaseオブジェクトには、直前で実行されたSQLの処理件数を格納する"RecordsAffected"というプロパティーがあります。
これを利用して、少々無精して次のようなプログラムを書いたのですが・・・明らかにデータの追加・削除が行われているにもかかわらず、すべて「処理件数0件」と表示されてしまいます・・・

CurrentDB.Execute "INSERT INTO ・・・"
Debug.Print "処理件数： " & CurrentDB.RecordsAffected & "件"

CurrentDB.Execute "DELETE FROM ・・・"
Debug.Print "処理件数： " & CurrentDB.RecordsAffected & "件"

調べたところ、Microsoftのドキュメントによれば"CurrentDB"の指定を使うたびに新しいDatabaseのインスタンスが作成され、それ以前の実行結果がクリアされるそうです。
そりゃ、いくら実行しても処理件数0件になりますよね

やはり、無精せずちゃんとオブジェクトを定義・設定したほうがよさそうで。

Dim db as Database
set db = CurrentDB()
db.Execute "INSERT INTO ・・・"
Debug.Print "処理件数：" & db.RecordsAffected & "件"
・・・

4. Replace関数の挙動

Replace関数を使って、少々ややこしい文字列の加工を行おうとしていた時のこと。
Replace関数の第四引数（オプション）で検索開始位置を指定できるので、プログラムで値を設定するようにしたのですが、なぜか思い通りの結果が得られません。

原因は、ずばり 「Replace関数の仕様」 でした。
例えば、Replace関数に開始位置を指定しないときは

Replace("あへあへふひは", "へ", "め")　⇒　"あめあめふひは"が返る

という挙動をするので、検索開始位置を指定すると

Replace("あへあへふひは", "へ", "め", 3)　⇒　"あへあめふひは"が返る

などという結果を期待してしまうんですが、実際には

Replace("あへあへふひは", "へ", "め", 3)　⇒　"あめふひは"が返る（指定文字より前はカット）

と返る仕様なのでした・・・

いちおうMicrosoftのマニュアルにも、らしいことは記されてはいるんですが、

Replace 関数の戻り値は、置換が行われた文字列で、start で指定された位置から始まり、式文字列の末尾で終了します。最初から最後まで元の文字列のコピーではありません。

などと、ちょっと「？」な記述です。
ともあれ、これはひとえに、人間側の「やったらあかん思い込み」の典型例でしょうか。

5. フォームでIME制御が効かない

特にAccessのバージョンアップや再インストールしたときに、たまに起こるエラー。
今までは、テキストボックスなどのフォーム要素で問題なく制御できていたにもかかわらず、突然制御が効かなくなる現象です。

原因は非常に分かりにくく、いまだに 「なんでやねん！」 感たっぷりなんですが・・・Accessのオプション設定。
「オプション」画面の「クライアントの設定」で
「データシート上でIMEを制御する」
のチェックを外すと直ります

6. 帳票フォームの項目をVBAで更新するときの実行時エラー

テーブルと紐づいた編集可能な帳票フォームで、表示しているテーブルカラムの内容をVBAを使って更新する処理があったんですが、ある日突然
「実行時エラー:3027　データベースまたはオブジェクトは読み取り専用なので、更新できません」
のエラーとなり、実行できなくなりました。

具体的には、次のようなコードです。
デバッグしたところ、6行目で編集モードに入ろうとするとエラーとなるようです。

Dim rs1 As DAO.Recordset
Set rs1 = Me.RecordsetClone    ' 自フォームのRecordsetCloneを設定
rs1.MoveFirst
Do While (Not rs1.EOF)
    If rs1![処理対象] = 1 Then
        rs1.Edit               ' ※ここでエラー
        rs1![チェック日付] = Now()
        ・・・・
        rs1.Update
    End If
    rs1.MoveNext
Loop

原因は実はこのコードではなくて、画面表示に使っているテーブルでした。
テストか何かの関係で
一時的に該当テーブルの主キーを削除
　　⇒VBA側で更新対象のレコードを特定できなくなってエラー
となったようです。

後で実験したところ、主キーだけでなくユニークインデックスの削除時にも同様の現象が起こるようですので、テーブルのメンテナンス時はくれぐれも気を付けて・・・

7. チェックボックス、ラジオボタンが立体表示されない

これは、つい最近まで悩まされてた問題です・・・

Accessをバージョンアップしたところ、チェックボックス、ラジオボタンの立体表示がいきなりできなくなりました。
今まで
　「立体表示：くぼみ」
を指定すると凹んだようなイメージになってましたが、いくらやってもメリハリのない平坦な見栄えです。

チェックボックスはまだしも、ラジオボタンとか鯉のぼりの目 or カエルの卵やん・・・

Accessのフォームに関して、「入力可能な項目の欄は凹ませる」というデザインルールにしていた筆者にとって、これはかなり深刻な問題です
ネットでもさんざん検索しましたが、解決策は皆無で。

半年以上にわたって調査＆試行錯誤してきて分かったのが、こちらもAccessのオプション設定の問題。
「現在のデータベース」タグで、「フォーム上のコントロールにWindowsのテーマを使用する」のチェックを外すと、見事元通りになりました！

・・・それにしても、たかだかこれだけのために、約半年手間取ったわけで。
バージョンアップしていろいろ機能や設定増やすのはええんですが、こういったデザイン面での 「下位互換性」 も、Microsoftはんにはちゃんと対応していただきたいもんです。

Accessプログラム よくある？トラブル集（単体編）