投稿者: nakamuran

バックエンドのデータベースに対してフロントエンドからVBAでアクションクエリを発行するとき

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Set cn=New ADODB.Connection
Set rs=New ADODB.Recordset
cn.ConnectionString=”接続文字列”
cn.open

rs.open “アクションクエリ文”,cn

のあとに
rs.close　と書くとエラーなのです。取っていないレコードセットを閉じるからですが
書かなきゃ通るからまあいいかとしていましたがだめらしいです
Dim cn as ADODB.Connection
Set cn=New ADODB.Connection
cn.ConnectionString=”接続文字列”
cn.open

cn.Execute “アクションクエリ文”

が正解なのか？でしょうね。

ACCESS2016のサンプルデータベースNorthwind.accdbで実験
テーブルの社員をMySQLへエクスポート　名前はsyainに変更し　ODBCでリンク
テスト用のプロシージャ作成

sub mytest()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
strSQL = “UPDATE syain SET syain.氏名 = ‘おれおれ’ WHERE syain.社員コード=?”

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = strSQL

cmd.Prepared=True
cmd.Execute , 110

cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing

End Sub

パラメータ付き更新クエリの発行です。
１回目は成功　そして何もせず続けて２回目実行
２回目以降は実行時エラー-2147467259　他のユーザーが同じデータに対して同時に変更を試みているので、プロセスが停止しました
他のユーザーって誰もいませんけど？
こういう仕様なんだろうと試行錯誤して

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
Dim Param As Long
strSQL = “UPDATE syain SET syain.氏名 = ‘おれおれ’ WHERE syain.社員コード=” & Param
Param = 105

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = strSQL

cmd.Prepared=True
cmd.Execute

cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing

End Sub
とパラメータをsql文の外に出すと競合エラーが出ないことがわかりました。
なぜ最初の構文で競合エラーかずっとわからなかったのですが
VBAからの更新クエリの返事にMySQLがaffected rowsを
返すかららしい。

ODBCのオプション変更でも回避できるようです。こちらの方が簡単ですね。
Return matched rows instead of affected rows
https://ameblo.jp/blueskyame/entry-10247966762.html

しかしまだわからない点があって
Return matched rows instead of affected rows
で競合エラーは出なくなりますがクエリを発行するたびに新しいODBC接続になります。
パラメータsql文外コードでは接続は増えません。

accessのバージョンでも挙動が異なるし難しい
たぶんACEになってからでしょうか
2000では何も問題はなかった。
any_windowsOS_64bit access2016_32bit odbc5.3unicode mysql5.7.17