【Access】クエリからＳＱＬを生成してVBAで動かす為のコツ

やりたい事は、

ということだけです。

しかし、これに１時間以上を費やしてしまったので備忘録認定にしました。

なぜ？クエリからＳＱＬを生成

クエリをそのまま実行すればいいじゃんって思うかもしれませんが、トランザクション処理して、複数個のクエリを実行してエラーでも出たものなら、データが最後まで戻らないという問題があるのです。

【Access】複数クエリをVBAでトランザクションをしてもロールバックしない問題を検証

それなので、仕方なく、ＳＱＬにしてＶＢＡに埋め込むような作業をやっているのです。

ひとつのクエリだけなら問題ないのですが、

ですね。

クエリをＳＱＬに生成したコードを修正

上の図のクエリを何も考えずに、ＳＱＬに生成すると下記のようになります。

UPDATE dbo_stock SET dbo_stock.schedule_arrival = [dbo_stock]![schedule_arrival]-[Forms]![arrival_product_run]![schedule_arrival]
WHERE (((dbo_stock.jan_sku_code)=[Forms]![arrival_product_run]![jan_sku_code]));

そのまま、ＶＢＡ内に埋め込めれば、どれだけ楽な事か！？

下記のように手を加えて実行しましたがエラーになります。

SQL = "UPDATE dbo_stock "                                      '在庫から出荷予定数を削除"
SQL = SQL & "SET dbo_stock.schedule_arrival = [dbo_stock]![schedule_arrival]-Forms![arrival_product_run]![schedule_arrival] "
SQL = SQL & "WHERE dbo_stock.jan_sku_code = Forms![arrival_product_run]![jan_sku_code]"

エラーは、ＳＱＬのコード内で出ているようなので、めぼしは、フォームの値の指定が間違っていると推測しました。

ＶＢＡ内でのフォームの値指定が難しい

SQL = "UPDATE stock "                                      '在庫から出荷予定数を削除"
SQL = SQL & "SET stock.schedule_arrival = schedule_arrival - '" & Me.schedule_arrival & "' "
SQL = SQL & "WHERE jan_sku_code = '" & Me.jan_sku_code & "'"

フォームの値を、カンマとダブルコーテーションなどで囲って指定するとコードのエラーは消えますが、求める値が出てきません。

しかし、

デバックプリントで値を確認すると、ちゃんと引っ張ってきています。

いったい何がダメなのか全くわかりませんでした。

フォームの値をＶＢＡ内のＳＱＬで使う場合のコツ

もし、フォームの値が

のように引っ張ってきていたとすれば、ＶＢＡのＳＱＬ内では、下記のようにすればいいようです。

いつも忘れてしまうのですよね。

フォームの値を使いたいケースって沢山あるので、この備忘録にしたので今度は忘れない事でしょう。

参考コード

Private Sub arrival_del_btn_Click()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim SQL As String
 
If MsgBox("本当に削除しますか？ yes/no", vbYesNo, "データ削除確認") = vbYes Then
  
                Set cn = CurrentProject.Connection
                Set cmd = New ADODB.Command
                cmd.ActiveConnection = cn
                  
On Error GoTo ErrorHandler
                  
            cn.BeginTrans
             
                SQL = "UPDATE dbo_stock "                                      '在庫から出荷予定数を削除"
                SQL = SQL & "SET dbo_stock.schedule_arrival = [dbo_stock]![schedule_arrival] - '" & Forms![arrival_product_run]![schedule_arrival] & "' "
                SQL = SQL & "WHERE dbo_stock.jan_sku_code = '" & Forms![arrival_product_run]![jan_sku_code] & "'"
                cmd.CommandText = SQL
                cmd.Execute

		'同時に処理させたいSQLコードを記入"
		cmd.CommandText = SQL
                cmd.Execute

		'同時に処理させたいSQLコードを記入"
                 
            cn.CommitTrans
                  
                Set cmd = Nothing
                cn.Close: Set cn = Nothing
  
        Else
                MsgBox "処理せずに終了しました。"
                Exit Sub
        End If
  
   
ExitErrorHandler:
                Exit Sub
          
          
ErrorHandler:
                cn.RollbackTrans
                Set cmd = Nothing
                cn.Close: Set cn = Nothing
                MsgBox "エラーが発生しました。処理せずに戻ります。"
                End

End Su