AccessからSqlServerExpressにリンクテーブルをした仕組みを構築しましたが、データが１万件を超えるようになってから、
ココがダメ
修正するだけでもエラーがでる！？
ようになってしまいました。
ODBC・・リンクテーブルでの更新に失敗
ある日突然、ODBCのエラーが出るようになりました。
なぜか？３２ビットAccessでは、エラーが出ない事が多かったりしましたが、６４ビットAccessでは１００％エラーが出現しました。
３２ビットAccessで作成したので、Microsoft365(64bit)にしたことで何かVBAコードを書き替えないといけないのかと調べまくりましたが、365Accessで開けて今回のエラー以外は問題なさそうなので原因は違うようでした。
※ネットで調べると６４ビット版にすることでVBAコードの修正が発生するような記事がありましたが自分の場合は関係なかったようです。
※VBAの参照設定なども確認しましたが自分の場合は関係ありませんでした。
更新VBAの見直し
今回の更新VBAが下記のようになります。
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
On Error GoTo ErrRtn
'アクションクエリ非表示設定
DoCmd.SetWarnings False
Set cn = CurrentProject.Connection
rs.CursorLocation = adUseClient
rs.Open "orderdata", cn, adOpenKeyset, adLockOptimistic
rs.Filter = "NO = " & edit_value
' トランザクションの開始
cn.BeginTrans
' 更新するフィールド
rs!shipper = Me!shipper_input
rs.Update
' トランザクションの保存
cn.CommitTrans
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothingつまり、NOにて、フィルターをかけて対象レコードを絞っています。
そして、下記の様なサイトを見つけました。
FindとFilterとはちょっと違うのかもしれませんが、Accessにとっては負荷のある関数なのかと考えてループで検索する事にしました。
今回の自分のケースでは、VBAコードをループにして、更新かけても問題解決にはなりませんでした。
ODBCの問題ではないようだ！
VBAコードに何か原因あるのだろうと推測をして、処理する部分をコメントアウトするとＯＤＢＣ更新エラーは無くなります。
こうなると、確実に、ＶＢＡに問題があるという回答になって行くかと思いますが、
ココに注意
Accessの限界じゃないかという推測もできます！
レコードが１万件を超えるころから処理に遅延が出始め、エラーも出始めました。
ＶＢＡの更新コードを、ＳＱＬに変更してみたり、ダメだとわかってもクエリーを試してみたりしました。
Accessというソフトは、リンクテーブルのデータを全部持ってきてから処理をするということを聞きましたが、それが本当ならばレコードが増えれば増えるほど使い物にならないって事になりますね。
SqlServer側で処理させることに変更
Accessに頼れないとすれば、SqlServerの方で処理させて、Accessで見る為のテーブルは、パススルークエリにするって事が必要かと思われます。
ココに注意
SqlServerの方で処理・・・ストアドプロシージャの設定が問題
何でもAccess側で設定ができるのがAccessの便利な所なのですが、限界じゃSqlServerのストアドプロシージャ（以下ストアド）を使うしか方法がありません。
でも、ストアドに慣れてない自分にとっては、ここからが無限地獄への入り口になりました。
エラーも出ないしデバッグ方法も不明
SqlServerのストアドの作成は、MSSMSにて行いましたが、
最新のMSSMSでは、デバッガーが無くなっているとか！？
じゃ何でデバッグすればいいんだよ！？VisualStudioだっせさ！
既に自分のPCにはインストールしてあったので、VisualStudioでデバックをやってみました。
ココがダメ
エラーも出ないしデバッグの方法も分からない（泣）
もうデバッグによる文法違反は諦めて、データだけを見る事にしました。
つまり、一つのテーブル内容から、別テーブルの内容を更新するストアドを実行させて更新されればOKという判断です。
ココがポイント
データ更新されないか、フリーズするという事でエラーと判断
しかし、本当にAccessからのストアドの呼び出し時にフリーズしたり、SqlServerの方でもクエリを動かしてフリースしたりと無限地獄です。
AccessでのVBAからストアドを実行するコードや、ストアドのコードを何度も見直しましたが、本当にエラーがでないでフリーズするので困りました。
２日かけ原因究明
とにかく、エラーが出ないのでどこが悪いのかが分かりません。
そして、２日目になってAccessのクエリを見ていたところ、一ヶ所だけおかしな記述が分かりました。
なんで、AccessのクエリとSqlServerのストアドとは関係ないだろ？って思われるかもしれませんが、自分の場合は、クエリでもテーブルの更新を実験して、更新する項目は、このクエリをＳＱＬにして、ストアドに利用していたりしているのでストアドとも関係があるのです。
つまり、このフィールドの設定は、ストアドでも間違っている可能性があるのです。
このクエリもフリーズせずに動く（かなり遅い）ようになりました。
ストアドの方も、動くようになりました。
USE [SGDB]
GO
/****** Object: StoredProcedure [dbo].[order_edit] Script Date: 2022/09/19 18:20:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[order_edit]
@eno VARCHAR(10)
AS
BEGIN TRY
BEGIN TRANSACTION
--ここから処理コード
UPDATE [order]
SET
flag = order_job.flag,
reception_date = order_job.reception_date,
reception_time = order_job.reception_time,
省略
deli_phone = order_job.deli_phone,
deli_notes = order_job.deli_notes
FROM [order] INNER JOIN
order_job ON [order].NO = order_job.NO
WHERE ([order].NO = @eno)
UPDATE [trader_ag]
SET
trader1 = order_job.trader1,
vehicle1 = order_job.vehicle1,
total1 = order_job.total1,
assistant1 = order_job.assistant1,
省略
deli_notes4 = order_job.deli_notes4,
notes5 = order_job.notes5,
deli_notes5 = order_job.deli_notes5
FROM [trader_ag] INNER JOIN
order_job ON [trader_ag].od_no = order_job.NO
WHERE ([trader_ag].od_no = @eno)
--ここまで処理コード
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_NUMBER()
END CATCH
RETURN 0;
MSSMSでのTips
パラメータ付ストアドのテスト方法
AccessVBAからパラメータ付で実行する仕組みですが、テストするのにAccessで行うのは大変です。
そこで、MSSMSからテストする方法がわかったので備忘録として書いておきたいと思います。
「対象データベース＞＞プログラミング＞＞ストアドプロシージャ＞＞実行するプロシージャ」を右クリックすると「プロシージャの実行」というのをクリックします。
値という場所に、パラメータ値を入れて実行すると、Accessから実行しなくてもSqlServerのストアドが実行されます。
自分の場合は、そのパラメータ値によって、１つのテーブルから、別テーブルのレコード値を更新するようにしましたので、結局、ちゃんと動いたかを判断するのは、テーブルの値をみることにはなりました。
他のデータベースにテーブルやストアドを移行
MSSMSに慣れてないので、ストアドのテストしてちゃんと動くことが確認できたら、本稼働しているデータベースに復元してから、テストした内容の設定をしなければなりません。
今回の場合は、テーブル１個とストアド２個を増設することで目的は果たせたのですが、本稼働しているデータベースに移行するのは大変です。
ココがポイント
ストアドプロシージャをスクリプト化
ストアドプロシージャをファイルにして保存できることがわかりました。
ファイルにしたスクリプトは、移行先のデータベースでファイルを読み込んでから、「クエリ実行」するだけでストアドに移行できました。
同じ様に、テーブルもスクリプト化できることがわかり同じようにして別のデータベースに移行することができました。
大きなデータベースの更新はストアドか！？
大きなと言っても１万レコードのデータベースですが、それでもAccessの場合は使い物にならないので、SqlServer側で処理するしかないようです。
今回の処理内容は、編集画面を出して編集するという普通に必要な処理ですが、大きなデータベースの場合は、下記の様なフローが必要でした。
（１）メインテーブルＡから対象レコードを選択（編集ボタンをクリック）
（２）編集ボタンをクリック時点で、対象レコードのＮＯをストアドにパラメータ付で実行。
（３）ＮＯより別テーブルＢに対象レコードをコピー※実行前にレコード削除
（４）編集画面起動（テーブルＢをソースとして現在のデータが表示）
（５）編集後編集ボタンをクリック（パラメータ付でストアド実行）
この流れで、データの更新をすることができました。
そして、圧倒的に更新スピードが改善されて、ＯＤＢＣ更新エラーになる事も無くなりました。
SqlServerとAccessの場合、本当にデバッグで苦労しますが、今回の経験から、１万件を超える様なレコードの場合での更新処理はストアドを使った方が良いという判断になるかと思います。
ただ、ストアドでのデバッグは、エラーが出ないのでテーブル値をみて判断するしかないというのが曲者です。
なんだか、最近、FileMakerServerも使う機会があるのですが、こんな苦労をするならFileMakerが良いような気にもなっています。