AccessVBAにてADOのUpdateでエラー

Question

Access2007のVBAでADOでMySQL8.0.20にODBC接続、ADOのRecordsetのUpdateメソッドで下記エラーが発生することがあります。

実行時エラー '-2147217864 (80040e38)':
行が見つからなかったため、更新できません。列の値は最後に読み込まれた後で変更された可能性があります。

　　Const adUseClient As Integer = 3
　　Const adOpenStatic As Integer = 3
    Const adLockOptimistic As Integer = 3
    
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = "DSN=社員台帳管理用;"
    cn.CursorLocation = adUseClient 
    cn.Open

    Dim sql As String
    sql = "SELECT * FROM 社員取得資格 WHERE 社員No = '" & Me!社員No & "' AND 資格No = '0001' ;"

    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, cn, adOpenStatic, adLockOptimistic

    With rs
        .Fields("運転免許証_種類").Value = Me!運転免許証_種類.Value '①
        If Nz(Me!失効年.Value, "") <> "" And Nz(Me!失効月.Value, "") <> "" And Nz(Me!失効日.Value, "") <> "" Then
            If IsDate(Me!失効年.Value & "年" & Me!失効月.Value & "月" & Me!失効日.Value & "日") = True Then
                .Fields("失効年月日").Value = Format(Me!失効年.Value & "年" & Me!失効月.Value & "月" & Me!失効日.Value & "日", "yyyy/mm/dd") '②
            End If
        End If
        .Update 'ここでエラー
        .Close
    End With
    
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

####コードの説明
recordsetの元になるSQL文のFROM句のテーブル「社員取得資格」のキーはWHERE句の「社員No」と「資格No」です。
recordsetには、一件レコードが入っています。
「運転免許証_種類」フィールドに、「運転免許証_種類」テキストボックスの値を代入。
「失効年月日」フィールドに、「失効年」「失効月」「失効日」コンポボックスの値を日付の形に加工してIsDateがTrueなら代入。
Updateして、終了。

####状況
エラー内容のように、レコードが探せなくなるようなことはないように見え、どう直せばいいかわからずにいます。

試した限りで、エラーが起きる条件があるようでした。
Updateメソッドの前にフィールドに値を代入しているソースの①、②について、
元のフィールドの値と同じ値がFieldsのValueプロパティに代入されている時に、Updateメソッドが実行されるとエラーが発生します。
また、当然ですが②がIf文で実行されなく、①で元のフィールドの値と同じ値をValueプロパティに代入するパターンもエラーです。

①、②どちらか一方でも、元のフィールドの値と違う値が代入されていれば、エラーは起きず正常に終了します。
さらに、①、②をコメントアウトして、Recordsetを開いて即Updateを実行するようなコードを試してみるとエラーは発生しませんでした。

この現象についてご存じの方がいらっしゃれば、教えていただきたいです。

###補足

元々Accessのみ使用でDAOを使用しており、MySQLを使用するためにDAOをADOに変更しています。
ADOに変更する中で、このエラーが発生しました。

また、他に少し気になることがあり、
ADODB.ConnectionのCursorLocationと
Recordsetを開くときのCursorTypeとLockTypeについて
いずれも、コード中では数値でいうと3を指定していますが、3以外の定数を指定しても、Debug.Printで確認すると3が指定されています。
ADODBをCreateObjectではなく参照設定で事前バインディングして行うと、CursorLocationとLockTypeについては好きな値を指定できますが、CursorTypeは必ず3になってしまいます。
この3つの値が意図しない値になる現象に困っている人はネットでもちらほら見かけますが、解決した記事が見当たらなく、どうしてそうなるのかわかっていません。
本題と関係あるかわかりませんが、一応補足です。

######解決時オプション設定
解決しました。解決時のオプション設定を共有致します。

MyODBC8.0.20の「Return matching rows」に値するオプションは画像の「Return matched rows instead of affected rows」というオプションでした。
以前のバージョンを使用していないのでおそらくですが、バージョンの違いで少し違うということでしょうか。
共有させていただきます。

Answer 1

競合に関するエラーの様ですね。
RecordSetを開いてループしているので、占有する時間も多くなりますから、RecordSetで処理するのではなくExecuteでupdate文を実行されるようにしてはどうでしょうか。

追記

接続オプションを変更してみてください。
ASP/VBでADOのレコードセット編集メソッドを利用する場合の注意点

Comments

[Finekaz]

ご回答ありがとうございます。
ExcuteのUpdate文なら確かに動きます！
ただDAOからADOに変える際に変更が容易なのでできればUpdateで行いたいのです。
できれば納得した上で選択したいので質問を2点程させてください。
①ループはしていないのですが、何か内部的な処理のことでしょうか？
②キー値が変わったりなどで行が探せなくなったりするなら理解できるのですが、一つのRecordsetで持っているだけでこのエラーが出るものなのでしょうか？占有している時間が長いと何か変わってしまうのでしょうか？
よろしくお願いいたします。

[sazi]

ループに関しては、ぱっと見でwithでのネストをEOFの判断だと思った勘違いです。
本題とは関係ないですが、selectの結果が無い場合は無いという前提なのですね。

[sazi]

conection のOptionsとして16387を指定してみてください

[Finekaz]

ループに関して了解致しました。
selectの結果は、無い場合、ありました。ご指摘ありがとうございます。

> ADOはeditが無い分updateが暗黙で行われることがある
そうなのですね。ADOのRecordsetは挙動が不安定になりそうですね。
もう少し回答を待ってみて、やはりダメそうなら、自分もExcuteのUpdate文で行おうと思います。

[sazi]

コメント編集したので補足。
> ADOはeditが無い分updateが暗黙で行われることがある
ADO.Recordsetのリファレンスに記載があります。
本件に直接関係があるかは分かりませんが、少なくとも他DBMSのの連携にはRecordsetの編集は積極的には使用しない方が良いと個人的には思っています。

[Finekaz]

connectionのOptions、試してみます。

[sazi]

発生しているエラーそのものは「Return matching rows」のオプション指定で解決しそうですけど、他のオプションについても、含めておいた方が良さげですね。

[Finekaz]

connectionのoptionsに16387を指定して実行したところ、RecordsetのOpenの行で
「実行時エラー3709 この操作を実行するために接続を使用できません。このコンテキストで閉じているかあるいは無効です。」というエラーが出ました。
調べたところ、すぐ解決しなさそうな感じのエラーですが、、わかりますでしょうか？

> ADO.Recordsetのリファレンスに記載があります。
そうでしたか。勉強不足でした。
> 少なくとも他DBMSのの連携にはRecordsetの編集は積極的には使用しない方が良いと個人的には思っています。
自分もそのような感じがしてきました。貴重なご意見、参考にさせて頂きます。

[sazi]

コネクションではなく接続DBの定義時点でないと駄目なのかもしれませんね。
DNSに設定してみてはどうでしょうか

[Finekaz]

すみません、16387指定する場所が全然違ったかもしれません。
ConnectionのOpenの行の第4引数Optionsに16387と書いていました。

リンク先のページのやり方を参考にODBCデータソースアドミニストレーター(からMySQL Connector/ODBCの画面)の方で設定しようと思いましたが、オプションが見つけられず、、MySQLではこのオプションがないのでしょうかね、、
ConnecionのConnectionStringに"DSN=社員台帳管理用;OPTION=16387;"と指定してみましたが、当初と同じ「行が見つからなかったため、更新できません。～」のエラーが出てしまいました。(OPTION=2も同様です。)
このページの内容、直りそうな気がしましたけどね、、

[sazi]

オプションとは別に、updateの代わりにUpdateBatchメソッドで改善しないでしょうか？

[sazi]

DNS指定すると、オプションはDNSを優先しているのかもしれません。
DNS経由では無く、ConnectionStringにoptionを含めて全て指定してみてはどうでしょうか

[Finekaz]

UpdateBatchというメソッドがあるのですね。
オプションありなしで試してみましたが、「行が見つからなかったため、更新できません。～」のエラーが出てしまいました。
長い間お返事して頂いていて、ありがとうございます。

[Finekaz]

なるほど、ドライバー名からの記入、試してみます。

[sazi]

UpdateBatchは単なる思い付きです。
挙動が変わってエラーにならない場合があるかと思いまして。
※根拠があるものでは無いので、上手くいったとしてもお勧めできるものではありませんが。
（最近のMSのリファレンスは非常に辛い）

[Finekaz]

ConnectionStringに
"Driver={mySQL ODBC 8.0 Driver};Server=127.0.0.1;Port=3306;OPTION=16387;Database=[データベース名];Uid=root;Pwd=[パスワード];"
と指定して実行してみたところ、当初と同じエラーでした、、
なかなかうまくいきませんね。

[sazi]

因みにDNSの設定ではAdvanced タブの表示はどのようなものですか？
以下では、それなりに表示されているようですが
https://manual.dreamer-site.net/mysql51/connectors.html

[Finekaz]

すみません、Advancedタブというのも表示されません。
画像を表示したいのですが、なぜか画像の挿入がうまく行われません。
他環境から画像挿入を試みます。

[sazi]

ドライバーバージョン確認とか別なPCにインストールしてみる方が早くないですか。

[Finekaz]

見ている画面がそもそも違っている場合を考え、画像を挿入しました。
Chromeをインストールし、Chromeからなら画像挿入できました。
Return matching rowsのオプションやAdvancedタブの話はこの画面とは違いますか？

[Finekaz]

次にPCに触るのが来週の火曜以降になるので、以降ご回答頂くものを試せるのがそれ以降になります。ご了承ください。

[sazi]

MySQLで確認する環境が手元にはありませんので、比較はできません。
他のタブの内容に該当するものがあったりしませんか？
先のコメントに書いたリンク先によると、オプションは２４個あるみたいなので、単にタブで分かれているだけな気がします。

[sazi]

MySQLのドライバーの話なので、MySQLタグ付けた方が識者の目に留まるかもしれませんね。

[Finekaz]

他のタブの中身も見たのですが、見つからなかったんですよね。
確かに。
MySQLのタグをつけます。

[sazi]

オプションのタイトルは微妙に変わっている感じですので、「Return matching rows」そのものではないかもしれません。
画像では、[Cursors/Results]タブが一番それらしい気がします。

[Finekaz]

お返事が遅くなり申し訳ありません。
もう一度MySQL Connector/ODBCに項目がないか探したところ、[Cursors/Results]タブに、「Return matched rows instead of affected rows」という項目があり、これにチェックを入れてVBA動作させたところ、問題が修正されました！
項目名、完全に同じものしか探していませんでした、お恥ずかしい限りです。
解決までお付き合いいただきありがとうございました。
saziさんをベストアンサーとさせていただきます。
これからExcuteで実行することを視野に入れどう更新処理するか決めたいと思います。

Answer 2

接続環境MySQL5.7/VB6ですが下記でやっています。
更新ではRecordsetは余り使っていなくて、更新処理はExcuteが中心です。
念のため、Return matching rows････(ODBC)へもチェック入れて使っています。

'Define
Public Conn As ADODB.Connection
Public Const pcstrCnndbguest As String = "DSN=testuser;PASSWORD=testpassword;DATABASE=testdb;OPTION=16387;"
'ADODB.Connection Create
Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
connectionString = pcstrCnndbguest
'MySQL Open
Conn.Open connectionString

＞.Fields("失効年月日").Value = Format(Me!失効年.Value & "年"･･･････
行を削除してもエラー出るんですよね。
これをすれば直るという答えは持っていませんが、下記Webを参考にしてMySQL設定環境条件に不足ないか見られたら如何かと思います。
https://www.mc-maniacs.com/wp-blog/notes-mysql_with_ms-access-163/
http://mysql.localhost.net.ar/doc/refman/4.1/ja/myodbc-clients.html
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html

Comments

[Finekaz]

ご回答ありがとうございます。お返事が遅くなり申し訳ありません。
問題が解決いたしました。
回答者saziさんのコメントの方に、解決した方法を示してあります。
指定していただいたリンク先は、今後の参考にさせていただきます。
ありがとうございました。