パソコン情報

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

 

やりたい事は、

ココがポイント

フォームの値を元に更新したクエリをVBAで実行させたい

ということだけです。

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

 

 

 

なぜ?クエリからSQLを生成

 

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

 

https://pctips.jp/pc-soft/access-query-transaction2020/

 

それなので、仕方なく、SQLにしてVBAに埋め込むような作業をやっているのです。

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

注意ポイント

ひとつの処理で複数個のクエリを実行するような場合は、SQLに生成してトランザクション付きのVBAにしないとダメ

ですね。

 

 

 

クエリをSQLに生成したコードを修正

 

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

 

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]));

 

そのまま、VBA内に埋め込めれば、どれだけ楽な事か!?

 

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

 

 

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のコード内で出ているようなので、めぼしは、フォームの値の指定が間違っていると推測しました。

 

 

 

VBA内でのフォームの値指定が難しい

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

 

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

しかし、

Debug.Print Me.schedule_arrival
Debug.Print Me.jan_sku_code

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

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

 

 

フォームの値をVBA内のSQLで使う場合のコツ

 

もし、フォームの値が

 

Forms![arrival_product_run]![schedule_arrival]

 

のように引っ張ってきていたとすれば、VBAのSQL内では、下記のようにすればいいようです。

 

'" & Forms![arrival_product_run]![schedule_arrival] & "'

 

ココがポイント

'" & フォームの値 & "'

 

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

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

 

 

参考コード

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