SqlServerとAccessにてシステムを構築しています。
ココがダメ
ある時から、レコードの更新するとすごい時間がかかるようになり、酷いときはODBC接続エラーになり更新することができなくなりました。
レコード件数が増えてODBCタイムアウト
SqlServerが無料ライセンスのExpress版を使用しているので色んな不具合が出ても自己解決させなければならない事は覚悟しています。
なので、ある程度のレコード件数になれば、遅延が出たりすることも理解できますが、２万レコード程度でODBCがタイムアウトになる！？
フィールド件数も２０件程度で、データ内容もテキストが少量です。Express仕様内に収まっていると思うのですが・・・
https://pctips.jp/server/sqlserver-stored-error/
https://pctips.jp/database/access-sqlserver-odbc-error/
どの程度レコード件数を減らせば更新できるのかテストしたところ、３０００件ほどにすれば、更新できる事がわかりました。
ちなみに、リンクテーブルを表示させることは可能でしたが、手動で編集をすると同じような現象が起きる事は確認しました。
ココがダメ
VBAの問題でななくODBC接続の遅さが問題なのかな！？
原因が本当に良く分かりません。
Accessの構成を考える
画面構成は、SqlServerからパスクエリーで引っ張ってきたレコードを表示させるようにして、修正したい時だけ、非連結の修正画面を出してVBAで更新させるような仕組みです。
更新方法は、下記サイトの方法で更新しました。
https://pctips.jp/pc-soft/access-vba-sql-edit/
摩訶不思議な方法で改善した！？
ココがダメ
未だに理由は分かりません！
ひょんな事から、
ポイント
リンクテーブルを手動で修正した時に、レコードを最終レコードに移動させてから先頭レコードに移動する動作を入れると更新できる事に気づきました。
ということで、レコード表示画面を読み込む時に下記の様なVBAを入れました。
そうすると今までのVBAでもODBC接続エラー無く更新する事ができました。
現時点では、２万レコードですが、この状態でどの程度までODBC接続エラーで更新できなくなるのか分かりませんが、様子をみて上限を見てみたいと思います。
しかし、SqlServerとAccessの世界は摩訶不思議な現象があるので、理解に苦しみます。
こんなテクニックの事は、どこにも記載されていないし、普通じゃ必要のないテクニックだと思います。
現在は、FileMakerなども使うようになり、SqlServer+Accessなどの使用用途が減りそうな感じもしますが、過去に構築してしまった仕組みに関しては摩訶不思議でも動いてくれればという思いで使うしかありません。