Chapter5-8
追加クエリ・更新クエリ・削除クエリといったアクションクエリをVBAから実行するには、主に次のようなケースによって処理方法を使い分けます。
- アクションクエリが”クエリ”としてデータベースに保存されている場合
- SQL文をVBAで動的に組み立てて処理する場合
■DoCmdオブジェクトのOpenQueryメソッドを使った方法
DoCmd.SetWarnings False
DoCmd.OpenQuery "qupd消費税計算"
DoCmd.SetWarnings True
DoCmd.OpenQuery "qupd消費税計算"
DoCmd.SetWarnings True
※この方法は、アクションクエリが”クエリ”としてデータベースに保存されている場合に使います。
※SetWarningsをFalseとすることで、アクションクエリ実行前の確認メッセージを表示させずに、そのまま即実行させることができます。
■SQL文で指定する場合（Databaseオブジェクトの「Execute」メソッドを使用）
Dim dbs As Database
Dim strRate As String
Dim strSQL As String
Beep
strRate = InputBox("消費税率をパーセントで入力してください。", "消費税計算", "5")
If Len(strRate) > 0 Then
Set dbs = CurrentDb
strSQL = "UPDATE tbl書籍情報 SET 消費税 = 価格 * " & Val(strRate) / 100
dbs.Execute strSQL
End If
Dim strRate As String
Dim strSQL As String
Beep
strRate = InputBox("消費税率をパーセントで入力してください。", "消費税計算", "5")
If Len(strRate) > 0 Then
Set dbs = CurrentDb
strSQL = "UPDATE tbl書籍情報 SET 消費税 = 価格 * " & Val(strRate) / 100
dbs.Execute strSQL
End If
※SQL文はVBAで自由に組み立てることができます。微妙に内容が変わるようなアクションクエリでは、個々のクエリを作って保存しておくのではなく、VBA上で変数と組み合わせてSQL文を作った方が利便的です。
※この方法では、アクションクエリ実行時のような確認メッセージは表示されません。必要に応じて、MsgBox関数で事前にユーザーに確認を促すようにします。
■パラメータクエリの処理
クエリがパラメータを持っている場合は、DoCmdオブジェクトのOpenQueryメソッドを使った方法には問題があります。アクションクエリ実行時に、パラメータを入力するダイアログが表示されてしまいます。
それを回避するため、パラメータクエリの場合には次のような書き方のプログラムを実行する必要があります。
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qupd消費税計算パラメータ")
With qdf
.Parameters("消費税率") = 5
.Execute
End With
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qupd消費税計算パラメータ")
With qdf
.Parameters("消費税率") = 5
.Execute
End With
VBAからパラメータクエリをダイアログ表示なしで実行するためには、パラメータへの値の代入もVBAで行う必要があります。
そのためにはまず、Databaseオブジェクトの「QueryDefs」コレクションから特定のクエリを「QueryDef」オブジェクトとして取り出します。QueryDefsコレクションには、そのデータベースに存在するすべてのクエリが格納されています。
そして、クエリのパラメータはQueryDefオブジェクトの「Parameters」コレクションにありますので、パラメータ名を指定して値を代入します。最後は、DatabaseオブジェクトではなくQueryDefオブジェクトの「Execute」メソッドを実行します。
また、パラメータを持った選択クエリのレコードセットを開く場合も、同様にParametersコレクションを設定した上で、QueryDefオブジェクトから引数なしでRecordsetオブジェクトを開きます。
Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qsel書籍情報パラメータ")
With qdf
.Parameters("抽出価格") = 3000
Set rst = .OpenRecordset()
.Close
End With
With rst
Do Until .EOF
Debug.Print !ISBN, !タイトル, !著者, !発行日, !価格
.MoveNext
Loop
.Close
End With
Dim qdf As QueryDef
Dim rst As Recordset
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qsel書籍情報パラメータ")
With qdf
.Parameters("抽出価格") = 3000
Set rst = .OpenRecordset()
.Close
End With
With rst
Do Until .EOF
Debug.Print !ISBN, !タイトル, !著者, !発行日, !価格
.MoveNext
Loop
.Close
End With
※特に選択クエリの場合は、この方法に限定されます。パラメータとして他のフォームのテキストボックスなどの値を参照している場合、たとえそのフォームが開いていても、OpenRecordsetメソッドで実行時エラーが発生してしまうためです。
