今回の記事では、Microsoft AccessでADOとリストボックスを使用して「インクリメントサーチ(逐語検索)」を実装する具体的な方法や実装におけるポイントを紹介していきます。
尚、今回の記事ではADOを使用してRDBMSへ接続し、Recordsetオブジェクトでデータを扱います。
当ブログではAccessやExcelのVBAでADOを利用する場合の細かい技術的な解説も別の記事で掲載しています。
良ければこちらもご参照ください。
インクリメントサーチ(逐語検索)とは？
業務アプリケーションでデータ検索機能を実装する場合、検索条件を入力するテキストボックスなどのコントロールと検索ボタンをフォームに設置して、ユーザーが検索ボタンを押下したイベントをトリガーにして、指定した条件に合うデータを取得するといった作り込みをするのが一般的ですが、「インクリメントサーチ」とは、検索エンジンなどにおけるサジェスト検索のように、文字入力の度にデータを取得してきて表示するような実装方法を指します。
参考までに、Wikipediaからも説明の一部を引用しておきます。
インクリメンタルサーチ（英語: incremental search）は、アプリケーションにおける検索方法のひとつ。検索したい単語をすべて入力した上で検索するのではなく、入力のたびごとに即座に候補を表示させる。逐語検索、逐次検索とも。
通常、単語の検索を行うには、検索したい文字列を完全に入力してから開始させなければならない。 しかし、インクリメンタルサーチでは、検索したい文字列を1文字入力するたびに自動的に検索を開始する。辞書アプリケーションなどではあてはまるものすべてがリストアップされ、エディタであれば即近の項目にジャンプする。文字を追加入力することにより絞り込みも行える。
このことにより、検索単語のすべてを打ち込む必要がなく、入力の誤りへの対処も即座に行うことができ、人間側の手間を省くことが期待できる。
インクリメントサーチ -Wikipedia-
業務アプリケーションではあまり使われない実装方法ですが、使いどころによっては、ユーザビリティは向上し、業務アプリケーションっぽくない動作を演出できるため、機会があれば是非試していただきたいです。
インクリメントサーチの実装サンプル
当項では、インクリメントサーチを実装するためのサンプルフォームの紹介と、実装時のサンプルコード、及び実装における注意点やポイントを紹介していきます。
今回の記事で使用するサンプルフォームとデータ
当記事のためにAccessのフォームを作成しつつ、テスト用のテーブルとダミーデータも作成しております。
それらも簡単に解説していきます。
サンプルフォームの解説
実際のフォームの外見は以下です。
当フォームのデザイナー画面で表示させつつ、インクリメントサーチを実装するうえで必要なプロパティの値も紹介しておきます。
尚、下記のプロパティ以外は既定値で結構です。
| プロパティ名 | 設定値 |
|---|---|
| [その他]名前 | txt_検索 |
| [データ]コントロールソース | 無し※非連結 |
| プロパティ名 | 設定値 |
|---|---|
| [その他]名前 | lst_結果一覧 |
| [データ]コントロールソース | 無し※非連結 |
| [データ]集合体ソース | 無し |
| [データ]集合体タイプ | テーブル/クエリ |
| [データ]連結列 | 1 |
| [書式]列数 | 3 |
| [書式]列幅 | 2cm;4cm;4cm |
使用するテーブルとダミーデータの解説
今回の記事では、以下のテーブルをMariaDBに作成しました。
| カラム名 | データ型 |
|---|---|
| ID | INT |
| 氏名 | VARCHAR(50) |
| 氏名よみがな | VARCHAR(50) |
尚、当記事の趣旨とはあまり関係ないのですが、当記事で実装したサンプルフォームの動作確認をするために、ダミーの個人情報を生成してくれる以下のウェブサービスを利用しました。
今回のサンプルでは1万件のデータを上記のテーブルに格納して動作を確認しております。
とても便利です。
インクリメントサーチを実装するサンプルコード
今回の実装例では、ADOでMariaDBと接続し、人の氏名でインクリメントサーチを行い、都度取得できたレコードセットをリストボックスに格納します。
尚、今回実装したサンプルコードでは、冒頭でも紹介したADO入門記事で使用したコードを流用して作成しております。
もしADO自体の実装方法も併せて確認したい場合は以下のリンク先も併せてご確認ください。
まず、「①テキストボックス」の「変更時」のイベントに以下の処理を実装します。
尚、この「変更時」のイベントに関する詳しい説明は以下のリンク先をご確認ください。
このリンク先でも説明がありますが、テキストボックスに対してキー入力を取得する場合のイベントは複数あり、具体的には以下の順番で動作します。
入力値の変更が発生した直後を取る場合は今回使用している「変更後(Change)」が最適です。
Private Sub txt_検索_Change()
Dim objCon As ADODB.Connection
Dim objRs As ADODB.Recordset
Dim ConString As String
Dim strSQL As String
Dim srvName As String
Dim dbName As String
Dim loginName As String
Dim loginPass As String
'データベース接続情報を定義します。
srvName = "192.168.1.1"
dbName = "sample_db"
loginName = "sample_user"
loginPass = "sample"
'接続文字列はご使用される環境に合わせて変更してください。
'※当例ではMariaDBと接続します。
ConString = "Driver={MARIADB ODBC 3.1 Driver};" & _
" server={" & srvName & "}; Port=3306; database=" & dbName & "; uid=" & loginName & "; pwd=" & loginPass & ";"
Set objCon = New ADODB.Connection
'既定のカーソルではダメ
objCon.CursorLocation = adUseClient
objCon.Open ConString
'テキストボックスに値がある場合のみSELECT文を実行します。
'テキストボックスのプロパティはValueではなく、Textを使用します。
If Me.txt_検索.Text <> "" Then
strSQL = "SELECT * FROM customer_demo WHERE 氏名 LIKE '%" & Me.txt_検索.Text & "%'"
Set objRs = objCon.Execute(strSQL)
End If
'Recordsetオブジェクトをリストボックスに代入します。
'テキストボックスの値が空の場合は空のRecordsetオブジェクトのまま代入します。
Set Me.lst_結果一覧.Recordset = objRs
Me.lst_結果一覧.Requery
objCon.Close
Set objRs = Nothing
Set objCon = Nothing
End Sub
上記のコードに抑えておくべきポイントを解説していきます。
26行目：
RecordsetオブジェクトのCursorLocationプロパティに対して「adUseClient」を指定しています。
CursorLocationの既定値はadUseServerのようですが、このカーソルではリストボックスへの代入時にエラーになります。
31行目：
テキストボックスの値を取得するために、テキストボックスの「Valueプロパティ」ではなく、「Textプロパティ」を使用しています。
テキストボックスから値を取得する場合はValueを使用するケースが多いのですが、今回の用途ではValueではなくTextを使用します。
この「ValueとTextの違い」についても簡単に説明しておきます。
まずはMicrosoftの公式のドキュメントから説明文を引用します。
コントロールにフォーカスがある間は、 Text プロパティには現在コントロールに配置されているテキスト データがあり、 Value プロパティには最後に保存されたコントロールのデータがあります。
要するに、Valueプロパティにはテキストボックスに入力されて確定した値(最後に保存された値)が入ります。
Textプロパティは現在入力中の値が入ります。
また、Textプロパティは現在アクティブなテキストボックスからしか取得できません。
今回のように、テキストボックスに入力中の値を都度取得したい場合はValueでは上手く取得できません。
よって、Textプロパティを使用する必要があります。
33行目：
前の条件分岐でテキストボックスに値があれば、この行でSELECT文を生成して、その次の行でSQLを実行してレコードセットを取得します。
尚、このSELECT文では、customer_demoテーブルの「氏名」カラムを参照し、現在のテキストボックスの値を条件にして、部分一致で取得するようにしてあります。
また、この条件分岐を入れないと、テキストボックスの値が空の場合にこのSQLの部分一致条件によりcustomer_demoテーブルの全行が取得されてしまうため、テキストボックスが空の場合はSQLを発行しないようにしてあります。
41行目：
前処理で取得したRecordsetオブジェクトをリストボックスのRecordsetプロパティに代入しています。
こうすることで、取得したRecordsetの行をリストボックスに表示させる動きを実現しています。
また、空のRecordsetオブジェクトが代入された場合は、リストボックスの表示も空になります。
ただし、元々リストボックスに行が存在するRecordsetオブジェクトが代入されていた状態で、空のRecordsetオブジェクトを上書きで代入するだけでは、即剤にリストボックスの表示を空にはできません。
空になるまでに数秒以上のタイムラグが発生します。
そこで、その次の行でリストボックスのRequeryメソッドを実行し、明示的に再読み込みをさせて即座に空になるようにさせています。
次に、おまけとして「②リストボックス」の「ダブルクリック時」のイベントに以下の処理を実装します。
Private Sub lst_結果一覧_DblClick(Cancel As Integer)
MsgBox "ID：" & Me.lst_結果一覧.Value
End Sub
この処理自体は、今回の記事で紹介したインクリメントサーチの実装には関係はありませんが、インクリメントサーチを実施してリストボックス内にRecordsetを表示させた状態で、どれかの行をダブルクリックすることで、リストボックスのプロパティの「連結列」で指定した列のValueを取得してメッセージボックスに表示させています。
私の場合は、リストボックスで複数行からどれか一つをユーザーにダブルクリックで選択させて、対象行のデータの詳細表示をしたり画面遷移をするといった実装をすることが多いので、参考として当サンプルコードに含めてみました。
今回の記事で紹介するインクリメントサーチの実装サンプルは以上になります。
是非お試しください。
最後に
今回の記事では、Accessでフォームを作成して、テキストボックスに入力した文字を元にインクリメントサーチを行い、検索結果を都度リストボックスに表示するといった実装方法の紹介を行いました。
尚、今回の紹介した実装方法における注意点は以下です。
- データ件数があまりに多いテーブルに対する参照処理では応答が遅くなる可能性がある。
- データベースに対してネットワーク越しにアクセスする場合に通信速度によっては応答が遅くなる。
- 部分一致条件でクエリを投げているため、インデックスが効かず全行走査になる。
インクリメントサーチでは、適切な応答速度で動作することが不可欠です。
今回の実装例では、文字入力の確定の都度SELECT文を生成してクエリを投げる仕組みであり、検索対象のデータの状態によっては、実用に耐えられるほどのレスポンスが発揮できない場合もあります。
その場合、いったん事前にローカルテーブルに目的のデータを移送したうえでネットワークを介さなくても検索できるようにしたり、予め対象となるデータを絞り込んだテーブルなどを作り、そこに対してクエリを投げたり、又はインクリメントサーチ自体を諦めるといったことも考える必要があります。
このように、どんなケースでも上手く実装できるとは限らないのですが、利用する状況や用途を間違えなければ、貴方の業務アプリケーションのユーザビリティは大きく向上する可能性もあります。
是非機会があれば試してみてください。
今回も記事を読んでいただきありがとうございました。
それでは皆さまご機嫌よう！
