三流君 ken3のmemo置き場

三流プログラマーのメモ書きです。主にVBAやWindowsの話題が多いです

挨拶・自己紹介:
失敗続きのAB型の変わり者 :三流プログラマー Ken3です
フリーのエンジニア・個人事業主です・・と書くと聞こえはイイが(それとなくカッコよく聞こえるが)、 現在は小さな案件の受注請負 と 短期派遣 で 日々つつましく?ほそぼそと暮らしてます。
よく検索されるキーワード: [質問回答XXXXさんへ] [CreateObject] [VBA] [JRA競馬オッズ]

Access パラメータークエリー に 値をVBAからセットするには?

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 を使用して AccessSQL パススルー クエリを作成する方法
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

Ken3 ホームページ 目次

分類:HPを大きく分けると4つの柱(分類)です。
・[Excel/Access VBA]の解説
・[ASP(Active Server Pages)]の解説。
・[元コンビニ店長時代の話]が弟に巻き込まれ、失敗した脱サラ、畑違い?の仕事で失敗。
・[プログラマーの愚痴]では、あまり見せたくない三流プログラマーの内面かな。
三流君を踏み台にする
主に上記4つの分類でHP作成やメルマガの発行を行ってます。
※更新頻度が落ちていて情報の鮮度が悪いです。



Googleを使用して検索する

読者の声:三流君の説明・解説じゃ よくわからなかったから、Googleを使って、自分で検索します。
三流君:残念です。あっ、下記にGoogleの検索窓を設置しました。
いろいろ指定して試してみてください。

Google
探す言葉:気になる単語や,オブジェクト(Document),プロパティ(.Busy)やメソッド(.Navigate)などを入れて検索してみてください。


言語を指定:見つからない時は指定無しで探す

サイト指定:人気QAサイト や 一次情報MS本家を指定する
一次情報・二次情報まとめから探る
QAサイトの質問から探る
検索実行: ←オプション確認後に検索ボタンを押してください

期間指定:情報の鮮度も大切?
検索実行: ←オプション確認後に検索ボタンを押してください


本当に三流なんです(笑):たまにスゴイですねなんて言われることもありますが、
真実は→ [三流君の真実は...] ←を初めに見てくださるとわかると思います。
(からくりは、成功例↑しか載せてなくて ヒドイ失敗例はお蔵入り迷宮入りが多かったりします)