【Access】定義域集計関数を使ったら重いのでSQLをVBAに組み込んで解消

定義域集計関数って何？

Accessを使っていますが、初めて聞くような関数というよりも、普段使っていても意識もしないような専門用語ですね。

定義域集計関数って何？

• DAvg … 平均を出す
• DCount …レコードカウント
• DFirst …先頭のデータを求める
• DLast … 最後のデータを求める
• DLookUp … 特定（検索、抽出）のデータを求める
• DMax … 最大値を求める
• DMin … 最小値を求める
• DStDev … 標準偏差を求める
• DStDevP … 標準偏差を求める
• DSum … 合計を求める
• DVar … 分散を求める
• DVarP … 分散の概算値

定義域集合関数の構文は、関数([expression], [domain], [criteria])

第1引数[expression]には集計または該当のデータを求めるフィールド名
第2引数[domain]にはテーブル名またはクエリ名を指定
第3引数 [criteria]は省略可能ですが、条件設定

自分が意識せずに使っている関数は、DlookupやDcountくらいしかありませんが、何でこの関数を調べようと思ったかと言えば、

からなんです。

フォームが開いてから、１０秒もかかる時がありました。これじゃ使いものになりません。

調べていくと、この定義域集計関数は、使う場所を選んだ方がよい場合があるようです。

私にはやる前から定義域集計関数を使った方が良いのかダメなのかの判別は尽きませんので、

って事だと思います。

定義域集計関数の方が簡単だけどSQL

今回の不満は、Dlookup関数の表示が遅いってだけなんです。

確かに、パススルークエリをクエリにかけた物をDlookup関数で検索させて、更に条件までつけていたので複雑と言えば複雑な感じでした。

しかたなく、VBAの中でSQLで処理をさせることにしました。

そして、Dlookup関数じゃなく、Dcount関数をVBA内で使用して求めたい答えを導き出しました。

本当に私は、SQLに関しては初心者なので、AccessのクエリをSQLに変えた物をそのまま使います。

フォームの値をパラメータにしたSQLが難しい

フォームの値をパラメータにしたクエリをSQLにしてVBAで動かそうとしてもなかなかうまく行きません。

いろいろとデバックしていくと、フォームの値がうまく引けてないようです。

HAVING (((Vew_order.arrange_date)=[Forms]![order_progress]![arrange_id]) AND ((Vew_order.progress)=1))

上のコードは、AccessのクエリをSQLに変えたそのままの物ですが、この状態だとうまく行きません。

これを、VBAのSQL内では、下記の様にします。

        SQL = SQL & "HAVING (Vew_order.progress = 1) and (Vew_order.arrange_date = '" & Forms![order_progress]![arrange_id] & "') "

特にフォームの指定は、

つまり、シングルカンマで囲み、ダブルコーテーションで囲み、＆マークで囲むってことが必要なのです。

※私の場合は、内容は数字ですが、形式が文字列なので文字列の場合です。

SQLをVBAに埋め込んで動かせたコード

Private Sub Form_Load()

 Dim cn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim SQL As String
 
        DoCmd.OpenForm "jobwait", acNormal, , , acFormEdit, acWindowNormal
        DoEvents
        
'        クエリをＳＱＬに変換して埋め込み

        SQL = "SELECT Vew_order.arrange_date, Vew_order.progress, Vew_product_stock.code, Vew_order.product_sku,  "
        SQL = SQL & "Vew_order.product_name, Sum(Vew_order.number) AS 出荷数, Vew_product_stock.schedule_arrival, "
        SQL = SQL & "Vew_product_stock.waiting_shipping, Vew_product_stock.stock "
        SQL = SQL & "FROM Vew_order INNER JOIN Vew_product_stock ON Vew_order.product_sku = Vew_product_stock.jan_sku_code "
        SQL = SQL & "GROUP BY Vew_order.arrange_date, Vew_order.progress, Vew_product_stock.code, Vew_order.product_sku, "
        SQL = SQL & "Vew_order.product_name, Vew_product_stock.schedule_arrival, Vew_product_stock.waiting_shipping, "
        SQL = SQL & "Vew_product_stock.stock "
        SQL = SQL & "HAVING (Vew_order.progress = 1) and (Vew_order.arrange_date = '" & Forms![order_progress]![arrange_id] & "') "
        SQL = SQL & "ORDER BY Vew_order.product_sku"



On Error GoTo Err_Handler

        'クエリの結果をフォームのレコードソースに設定
 
    Set cn = CurrentProject.AccessConnection
    Set rs = New ADODB.Recordset
 
            With rs
            Set .ActiveConnection = cn
            .Source = SQL
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .Open
            End With
 
        Set Me.Recordset = rs
        rs.Close: Set rs = Nothing
        cn.Close: Set cn = Nothing
 
 
ExitErr_Handler:
                DoCmd.Close acForm, "jobwait", acSaveNo
                
'                条件によって算出したものをフォームに件数として表示
                Judgment = DCount("stock", "stock_inquiry", "stock<([出荷数] + [waiting_shipping])")

        Exit Sub
 
Err_Handler:
        DoCmd.Close acForm, "jobwait", acSaveNo
        MsgBox "エラー： " & Err.description
        rs.Close: Set rs = Nothing
        cn.Close: Set cn = Nothing

End Sub

JudgmentというテキストボックスにDlookupやDcountを入れて求めたい数値を出していましたが、上のコードのようにして、VBA内のDcountで処理をしています。

定義域集計関数に関しての自分ルール

もちろん、使った方がいいのか？ダメなのか？の判断なんてつきやしませんが、何となくわかったことは、定義域集計関数を使うなら、

という事でしょうかね。

どうしても複雑になるような計算をさせる場合は、VBA内でSQLにした仕組みに変えるって事だと思います。