中級者のためのExcel エクセルマクロVBA：Accessデータベースから検索する

検索機能を作る

連休半ばになにやってんの？と言われそうですが、何のことはなくカレンダー通りの私です。SQLについてはそれだけでもうえらい数ブログ書けるのですが、一応VBAブログなので前回ので割愛。後は実践で書いて行く中で覚えていきましょう。

では、さっそく作ったデータベースを使って、検索機能を作りましょう！簡単です。条件を書いて一覧に出すだけです。一覧の画面は下に置いておきます。

検索ボタンを押すとその下の一覧画面に商品テーブルの内容を出力する。というものです。デザインとかぶっちぎってるので、みなさんは後は良しなにしてください。ここでは、いくつかのActiveXオブジェクトを利用しています。画面を見るとわかりますが、テキストボックスです。別にこのようなオブジェクトを使わなくても、セルに名前を付けても構わないです。要するに、

ボタンをを押したら入力された検索条件を変数に格納できればそれでOK

ということです。今回はテキストボックスオブジェクトを利用しているので、名前だけ一応整理して付けておきます。オブジェクトの名前はオブジェクトをクリックして選択し、右クリックからプロパティを選択すると、プロパティウィンドウが表示されますので、そこで設定できます。

・商品IDの入力用：txtProductId
・商品名の入力用：txtProductName
・価格の入力用：txtPriceFromとtxtPriceTo

です。このオブジェクトのValueプロパティを参照すると値が取得できます。では、前々回くらいに書いたコードを改良して、ボタンを押したら条件に合致するものを出力するマクロを書いてみます。

Option Explicit

Private Sub ProductSearchButton_Click()
    
    Dim mCon As ADODB.Connection             'データベース接続オブジェクト
    Dim mRes As ADODB.Recordset              'レコードセットオブジェクト
    
    Dim ret As Variant
    Dim ret2 As Variant
    Dim Sql As String
    
    Dim i As Long, j As Long
    
    Dim productId As String
    Dim productName As String
    Dim priceFrom As String
    Dim priceTo As String
    
    ' 検索条件を取得する
    productId = Sheet2.txtProductId.Value
    productName = Sheet2.txtProductName.Value
    priceFrom = Sheet2.txtPriceFrom.Value
    priceTo = Sheet2.txtPriceTo.Value
    
    ' DBに接続する
    Set mCon = mdlDbUtil.initDb
    
    
    '''''''''''''''検索する'''''''''''''''
    'レコードセットオブジェクト作成
    Set mRes = New ADODB.Recordset
    
    ' 検索するSQL
    Sql = p_setSqlProduct(productId, productName, priceFrom, priceTo)

    ' 検索する（検索するとレコードセットオブジェクトに検索結果が格納される。
    mRes.Open Sql, mCon, adOpenKeyset, adLockReadOnly
    
    ' 出力前に出力エリアをクリア
    Sheet2.Range(Sheet2.Cells(14, "A"), Sheet2.Cells(Rows.Count, "C")).ClearContents
    
    i = 14
    Do Until mRes.EOF
    
        '出力する
        Sheet2.Cells(i, "A").Value = mRes.Fields("id").Value
        Sheet2.Cells(i, "B").Value = mRes.Fields("product_name").Value
        Sheet2.Cells(i, "C").Value = mRes.Fields("price").Value

        '次のレコードへ移る
        mRes.MoveNext

        '出力箇所カウンタ+1
        i = i + 1
    Loop
    
    'DB切断
    mCon.Close
    Set mCon = Nothing

End Sub

これと・・・SQL文を作るメソッドを別に作っています。

Private Function p_setSqlProduct(ByVal productId As String, ByVal productName As String, _
                                ByVal priceFrom As String, ByVal priceTo As String) As String
                                
    Dim strWhere As String
    Dim Sql As String
    
    Sql = "SELECT * FROM Product "
    
    strWhere = "WHERE "
    If productId <> "" Then
        
        strWhere = strWhere & "ID = '" & productId & "'"
        
    End If
    
    If productName <> "" Then
        If strWhere <> "WHERE " Then
            strWhere = strWhere & "AND PRODUCT_NAME LIKE '%" & productName & "%'"
        Else
            strWhere = strWhere & "PRODUCT_NAME LIKE '%" & productName & "%'"
        End If
    End If
    
    If priceFrom <> "" Then
        If strWhere <> "WHERE " Then
            strWhere = strWhere & "AND PRICE >= " & priceFrom
        Else
            strWhere = strWhere & "PRICE >= " & priceFrom
        End If
    End If
    
    If priceTo <> "" Then
        If strWhere <> "WHERE " Then
            strWhere = strWhere & "AND PRICE <= " & priceTo
        Else
            strWhere = strWhere & "PRICE <= " & priceTo
        End If
    End If
    
    If strWhere <> "WHERE " Then
        Sql = Sql & strWhere
    End If
    
    p_setSqlProduct = Sql
    

End Function

さらにデータベースへの接続系はどこでもやるので、標準モジュールに移しました。

標準モジュールをmdlDbUtlとして・・・

Option Explicit

Public Function initDb() As ADODB.Connection
    Dim connectionString As String          '接続文字列
    Dim dbFilePass As String                'データベースファイルのパス
    
    Dim mCon As ADODB.Connection             'データベース接続オブジェクト
    Dim mRes As ADODB.Recordset              'レコードセットオブジェクト
    Dim strCon As String
    Dim ret As Variant
    Dim ret2 As Variant
    Dim Sql As String
    
    Dim i As Long, j As Long
    
    '接続文字列作成
    connectionString = "Microsoft.ACE.OLEDB.12.0"
    dbFilePass = "C:\zDevelop\VBA\SampleDb.accdb"
    
    
    'オブジェクト作成
    Set mCon = New ADODB.Connection
    
    ' 接続文字列を作成する
    strCon = "Provider=" & connectionString & ";" & "Data Source=" & dbFilePass & ";"
    
    '接続する
    mCon.Open strCon
    
    Set initDb = mCon

End Function

はい、こんな感じです。これで、検索フォームに入れると自由に検索して、その結果を一覧に表示してくれます。データベースへの接続は毎回行うことになるので、今回のように標準メソッドにしてしまうといいです。ほんとはクラス化するともっといいです。接続から切断までまとめてやれるので。コード量は多く見えるかもしれませんが、処理を分けて書いているので、分かりやすくなっていると思います。惑わされずに読んでください。

またSQL文を作っているところがありますよね？これ結構面倒くさいことやってます。検索のパラメータがいくつもあるので、、、

・商品IDが入力されてる？
　・されていたら、条件に追加しましょう。
・商品名が入力されている？
　・最初の検索？そうじゃない？
　　・最初の追加として設定
　　・ANDでつないで条件に追加
・値段～が入ってる？
　・最初の検索？そうじゃない？
　　・最初の追加として設定
　　・ANDでつないで条件に追加
・～値段が入ってる？
　・最初の検索？そうじゃない？
　　・最初の追加として設定
　　・ANDでつないで条件に追加

みたいなことをしています。条件はWHERE句で追加の条件はANDまたはORでつなぐのでこのようなことをしているわけです。後は出力ですが出力の条件は毎回変わるので、一旦出力エリアをクリアしています。

いかがでしょうか？なんか、システムっぽくなってますよね？次は商品の登録をやってみましょう！

今日はここまで。

かしこ