VBA コードから クエリーにパラメーターをセットしてみたかったので、今回テストしてみます。
手抜き解説と実行結果です : http://www.youtube.com/watch?v=FzS3nvyY7mw
www.youtube.com
↑後半の開けなかった、エラーがとても気になるけど・・・
1.事前準備でクエリーに細工する
1.1 パラメータ(条件)に名前をつけます
抽出条件の所に[選択ID]みたいに、[]で名前を付けます。
ここで付けた名前(ここでは[選択ID])に対して、VBAから値をセットしてみます。
1.2 パラメータの型を定義します。
右上のパラメーターを押すと、パラメーターの型を指定するダイアログが表示されます。
適切な型を指定します。
2.VBAからパラメータをセットしてみる
VBAから可変のパラメーターをセットするサンプルなのに、
今回は、パラメーターとして A1 を セットしてみます(ぉぃぉぃ固定かよ(笑))
2.1 参照設定を忘れずに
参照設定を行います。
MIcrosoftActiveX Data Object x.x Library
と
MIcrosoft ADO Ext.x.x for DDL and Security
を参照設定します。
2.2 作成したコード
ADOX を使用して Access で SQL パススルー クエリを作成する方法
http://support.microsoft.com/kb/304323/ja
や
セットとチョット違うけど
Parameters コレクション、Command プロパティの例 (VB)
http://msdn.microsoft.com/ja-jp/library/cc389624.aspx
を参考に探りを入れる。
(※ ADOX.Catalog ADODB.Command .Procedures で 日本語サイトを検索するとサンプルがザクザク出てきますよ)
下記、テストで作成したコード。
Private Sub コマンド0_Click() 'パラメータークエリーに値をセットする Dim cnn As New ADODB.Connection '接続 Dim cat As New ADOX.Catalog '※参照設定 MIcrosoft ADO Ext.x.x for DDL and Security Dim cmd As ADODB.Command 'コマンドの中にパラメーター?がある? Dim rs As New ADODB.Recordset 'いつものレコードセット cnn.Open CurrentProject.Connection '自分のプロジェクトと接続 'クエリーにパラメータをセットする cat.ActiveConnection = cnn Set cmd = cat.Procedures("Q入札明細のクロス集計").Command cmd.Parameters("[選択ID]") = "A1" '課のコードをセットする '↑パラメータセット後、クエリーをリード専用で開く rs.Open cmd, , adOpenStatic, adLockReadOnly, adCmdText '開いたクエリーのフィールド名をテストで表示 Dim x As Integer '列カウンター、フィールド数 'フィールド名の取り出し For x = 0 To rs.Fields.Count - 1 '0からなので、-1まで回す Debug.Print rs.Fields(x).Name & rs.Fields(x).Value Next '後始末 クローズと使用変数をNothing rs.Close cnn.Close Set rs = Nothing Set cmd = Nothing Set cnn = Nothing End Sub
3.コンボボックスの値が変わったら、クロス集計してみた
コンボボックスの値が変更されたら、
コンボボックスの値を利用して(セットして)、
クロス集計クエリーを作ってみました。
cmd.Parameters("[選択ID]") = Me![担当課の選択] '課のコードをセットする
↑ここにセットしただけでした。
試行錯誤の動画
手抜き解説と実行結果です : http://www.youtube.com/watch?v=FzS3nvyY7mw
www.youtube.com
↑後半の開けなかった、エラーがとても気になるけど・・・
下記、テストで作成したコードです
Private Sub 担当課の選択_AfterUpdate() '担当課が選択されたら、 'パラメータークエリーに値をセットする Dim cnn As New ADODB.Connection '接続 Dim cat As New ADOX.Catalog '※参照設定 MIcrosoft ADO Ext.x.x for DDL and Security Dim cmd As ADODB.Command Dim rs As New ADODB.Recordset 'いつものレコードセット cnn.Open CurrentProject.Connection '自分のプロジェクトと接続 'クエリーにパラメータをセットする cat.ActiveConnection = cnn Set cmd = cat.Procedures("Q入札明細のクロス集計").Command cmd.Parameters("[選択ID]") = Me![担当課の選択] '課のコードをセットする Debug.Print cmd.CommandText '↑パラメータセット後、クエリーをリード専用で開く rs.Open cmd, , adOpenStatic, adLockReadOnly, adCmdText 'テストで文字列で表示 Dim strWORK As String Dim x As Integer '列カウンター、フィールド数 If rs.RecordCount = 0 Then strWORK = "データがありません" Else '開いたクエリーのフィールド名をテストで表示 'フィールド名の取り出し strWORK = "" For x = 0 To rs.Fields.Count - 1 '0からなので、-1まで回す strWORK = strWORK & rs.Fields(x).Name & vbTab Next '値の取り出し While rs.EOF = False strWORK = strWORK & vbCrLf '1レコード単位で改行、 For x = 0 To rs.Fields.Count - 1 '0からなので、-1まで回す strWORK = strWORK & rs.Fields(x).Value & vbTab 'タブで区切る Next rs.MoveNext Wend End If '後始末 クローズと使用変数をNothing rs.Close cnn.Close Set rs = Nothing Set cmd = Nothing Set cnn = Nothing '結果のテスト表示 Debug.Print strWORK MsgBox strWORK End Sub