この記事では、Recordsetで取得したSQL Serverのテーブルデータをフォーム上に設置されたListboxに表示させる方法についてご説明します。
【動画】Recordsetで取得したSQL Serverのテーブルデータをフォーム上に設置されたListboxに表示させる実際の動き
本題に入る前に、まずは次の動画をご覧ください。
SQL ServerのテーブルデータをRecordsetで取得し、フォーム上に設置されたListboxに表示させています。
マクロ作成の流れ
コードの例
Excelのマクロのコード(例)
Option Explicit Private Sub UserForm_Activate() Dim connDB As String 'データベース接続情報 Dim objConnection As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数 Dim oRS As ADODB.Recordset 'レコードセット用変数 Dim sqlStr As String 'SQL文 Const DBName As String = "testDB1" 'データベース名 'データベース接続情報を取得 connDB = "Provider=SQLNCLI11.1;" connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;" connDB = connDB & "Initial Catalog=" & DBName & ";" connDB = connDB & "Trusted_Connection=yes;" 'DBへの接続 Set objConnection = New ADODB.Connection objConnection.CursorLocation = adUseClient objConnection.Open connDB 'Recordsetオブジェクトのインスタンスを生成する Set oRS = New ADODB.Recordset '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、 'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定 oRS.ActiveConnection = objConnection 'SQL Serverからテーブルデータを取得するSQL文を作成 sqlStr = "select * from dbo.PrefecturesList " sqlStr = sqlStr & " ORDER BY 1 ASC" 'データを抽出するSQL文をSourceプロパティに設定する oRS.Source = sqlStr '指定したテーブルのデータを参照する(アクセスする) oRS.Open With UserForm1.ListBox1 'データの数分列を表示する .ColumnCount = 2 'SQL Serverのテーブルから取得したデータをListboxに表示させる .Column = oRS.GetRows End With 'Connectionを閉じる oRS.Close objConnection.Close Set oRS = Nothing Set objConnection = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは13行目から21行目です。
'データベース接続情報を取得 connDB = "Provider=SQLNCLI11.1;" connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;" connDB = connDB & "Initial Catalog=" & DBName & ";" connDB = connDB & "Trusted_Connection=yes;" 'DBへの接続 Set objConnection = New ADODB.Connection objConnection.CursorLocation = adUseClient objConnection.Open connDB
13行目から16行目でSQL Serverのデータベースに接続するのに必要な情報を取得します。
19行目でConnectionオブジェクトを生成したら、21行目のOpenメソッドに先ほど取得したSQL Serverのデータベースに接続するのに必要な情報を引数に指定してOpenメソッドを実行します。
Openメソッドの実行が成功すると、ExcelのマクロがSQL Serverのデータベースに接続されます。
注目すべきコード②
次に見て頂きたいのは24行目です。
'Recordsetオブジェクトのインスタンスを生成する Set oRS = New ADODB.Recordset
24行目でRecordsetオブジェクトのインスタンスを生成します。
インスタンスが生成されると、Recordsetを扱うことができます。
注目すべきコード③
次に見て頂きたいのは29行目です。
'先ほど取得したデータベース接続情報が設定されているobjConnectionをActiveConnectionプロパティに設定し、 'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定 oRS.ActiveConnection = objConnection
29行目では、先ほど取得したデータベース接続情報が設定されているobjConnectionをActiveConnectionプロパティに設定し、ConnectionオブジェクトとRecordsetオブジェクトを関連付けます。
以上のコードを実行することで、SQL Serverのテーブルデータを取得するSelect文(※このあとの「注目すべきコード④」で説明します)を実行してテーブルデータを取得することができます。
注目すべきコード⑤
次に見て頂きたいのは32行目から39行目です。
'SQL Serverからテーブルデータを取得するSQL文を作成 sqlStr = "select * from dbo.PrefecturesList " sqlStr = sqlStr & " ORDER BY 1 ASC" 'データを抽出するSQL文をSourceプロパティに設定する oRS.Source = sqlStr '指定したテーブルのデータを参照する(アクセスする) oRS.Open
32行目と33行目では、SQL Serverのテーブルデータを取得するSQL文を用意します。
36行目では、先ほどのSQL文をSourceプロパティに設定します。
39行目ではOpenメソッドを実行し、SQL Serverからテーブルデータを取得します。
注目すべきコード④
次に見て頂きたいのは41行目から49行目です。
With UserForm1.ListBox1 'データの数分列を表示する .ColumnCount = 2 'SQL Serverのテーブルから取得したデータをListboxに表示させる .Column = oRS.GetRows End With
上記は、Listboxに関する設定を行っているコードです。
47行目では、GetRowsメソッドを実行してRecordsetで取得したSQL ServerのテーブルデータをListboxのColumnプロパティに設定することで、フォーム上に設置されたListboxにSQL Serverのテーブルデータを表示させています。
動作確認
今回の動作確認用に、SQL Serverのデータベースファイルに都道府県の名称が保存されているテーブル「PrefecturesList」を用意しました。
この都道府県の名称を、Excelのフォーム上に設置されたListboxに表示させます。
マクロ実行前
下のExcelは、マクロを実行する前の状態です。
マクロ実行後
マクロを実行すると、下の画像のように都道府県の名称がExcelのフォーム上に設置されたListboxに表示されています。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelマクロのコードの6行目「ADODB.Connection」と7行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim objConnection As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数 Dim oRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、Recordsetで取得したSQL Serverのテーブルデータをフォーム上に設置されたListboxに表示させる方法についてご説明しました。
SQL Serverのテーブルデータをフォーム上に設置されたListboxに表示させたい場合、Recordsetを使うと簡単に表示させることができます。是非参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。