VBAでDBの更新処理のマクロを実行しようとするとエラーがでる

Question

エクセルでVBAによってSQLでUPDATEを実行したいのですが
以下のVBAコードでマクロを実行した所「オブジェクトが閉じている間は操作が許可されません」というエラーが出てしまいました。

Sub ボタン1_Click()
Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim SQL As String    ' SQL
  Dim i As Long
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")
 
 On Error GoTo ErrorTrap
 
  ' ODBC接続
  adoCon.Open _
      "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
      " SERVER=localhost;" & _
      " DATABASE=abc;" & _
      " UID=root;" & _
      " PWD=root;"
 
  MsgBox "DB接続成功"
 
  ' SQL文
SQL = "UPDATE `mail_db` SET `mail_level` = '10' WHERE `mail_db`.`mail_id` = 10"
 
  ' SQLの実行
  Set adoRs = adoCon.Execute(SQL)
  
  ' レコードセット内の全ての行の読込が
  ' 終了するまで処理を繰り返す
  i = 2
  Do Until adoRs.EOF
    Cells(i, 1) = adoRs!mail_id
    Cells(i, 2) = adoRs!mail_from_name
    Cells(i, 3) = adoRs!mail_subject
    'Cells(i, 4) = Format(adoRs!addDate, "yyyy/mm/dd")
    
    i = i + 1
    ' 次の行に移動する
    adoRs.MoveNext
  Loop
 
  ' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing
Exit Sub
 
ErrorTrap:
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

調べてみるとSQLを複数回呼び出していることが原因とか聞いたり、「SET NOCOUNT ON」と記入すれば良いとか聞いたので記述してみたのですが解決できませんでした。

この「SET NOCOUNT ON」を記述することで解決できるのであれば上のコードへの挿入箇所、またはそれ以外のコードの記述方法等があれば教えていただきたいです

Answer 1

MSDNのADO Executeメソッドの説明に

Connection オブジェクトで Execute メソッドを使用すると、指定した接続上で、CommandText 引数に指定したクエリが実行されます。CommandText 引数に、行を返すクエリが指定されている場合、クエリの実行結果はすべて新規 Recordset オブジェクトに保存されます。行を返すクエリ以外のコマンドの場合は、プロバイダは閉じた Recordset オブジェクトを返します。

とあります。

SQLがUPDATE文なので、"閉じたRecordset"が返ってきてるのではないでしょうか。
※mysqlのupdateは変更の件数が返ってくるもので、recordsetは取得できないのかなと想像してます

SQLをSELECT文に変更して、recordsetのupdateと値取得をするように修正するのではダメですか？
または、UPDATE文とSELECT文を、わけて実行してみてはどうでしょう。

Comments

[m_s]

返答が遅れて申し訳ないですやってみようと思います