SQLのSELECT文で外部データベースからデータを取得する方法
Excel VBA でSQL文を使用して、外部データベースのレコードを取得するには、ActiveX Data Objects【ｱｸﾃｨﾌﾞﾃﾞｰﾀｵﾌﾞｼﾞｪｸﾂ】（以下ADO）のライブラリーファイルへの参照設定を行い外部データベースの各オブジェクトのインスタンス（複製）を作成して各メソッドやプロパティを使用して外部データベースのテーブルのレコードを取得します。
SELECT文で外部データベースからデータを取得する手順
1. Excelと外部データベースの接続を表す Connection【ｺﾈｸｼｮﾝ】オブジェクトのインスタンスを作成してオブジェクト変数に格納します。
2. 外部データベースのテーブルのレコードを表すRecordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトのインスタンスを作成してオブジェクト変数に格納します。
3. データベースに対して問い合わせを行うSQL実行を表す Command【ｺﾏﾝﾄﾞ】オブジェクトのインスタンスを作成してオブジェクト変数に格納します。
4. Connection【ｺﾈｸｼｮﾝ】オブジェクトのOpen【ｵｰﾌﾟﾝ】メソッドを使用して外部データベースに接続します
5. Command【ｺﾏﾝﾄﾞ】オブジェクトのActiveConnection【ｱｸﾃｨﾌﾞｺﾈｸｼｮﾝ】プロパティ
に操作対象のConnection【ｺﾈｸｼｮﾝ】オブジェクト（外部データベース）を設定します。
6. Command【ｺﾏﾝﾄﾞ】オブジェクトのCommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティ
に実行するSQLのSELECT【ｾﾚｸﾄ】文を設定します。
7. 実行を表す、Command【ｺﾏﾝﾄﾞ】オブジェクトの
Execute【ｴｸｽｷｭｰﾄ】メソッドを使用してSELECT文を実行します。
なお、SELECT文を実行するとExecuteにはSELECT文の抽出結果が格納されているRecordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトが返されるのでRecordsetオブジェクトのインスタンスが格納されているオブジェクト変数に代入します。
8. Recordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトに格納されているすべてのレコードをコピーするRangeオブジェクトのCopyFromRecordset【ｺﾋﾟｰﾌﾛｰﾑﾚｺｰﾄﾞｾｯﾄ】メソッドを使用してSELECT文で抽出したレコードを指定のワークシートに貼り付けます。
SQLのSELECT文でAccessの「商品マスター」テーブルの
レコードをすべて、ワークシートに抽出する例
コードと解説
※ADOのライブラリファイルへの参照設定がされていることが前提のコード例です。
Sub SELECT文()
Dim コネクション As New ADODB.Connection
Dim レコード As New ADODB.Recordset
Dim コマンド As New ADODB.Command
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"SELECT * FROM 商品マスター;"
Set レコード = .Execute
End With
Range("A1").CopyFromRecordset Data:=レコード
レコード.Close: Set レコード = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub
Dim【ﾃﾞﾑ】ステートメントでNew【ﾆｭｰ】キーワード使用して外部データベースとの接続を確立するConnection【ｺﾈｸｼｮﾝ】オブジェクトのインスタンスを生成して、オブジェクト変数「コネクション」に格納します
3行目【Dim レコード As New ADODB.Recordset】
Dim【ﾃﾞﾑ】ステートメントでNew【ﾆｭｰ】キーワード使用して外部データベースのレコードを参照するRecordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトのインスタンスを生成してオブジェクト変数「レコード」に格納します。
4行目【Dim コマンド As New ADODB.Command】
DimステートメントでNewキーワード使用してSQL文の実行を表すCommand【ｺﾏﾝﾄﾞ】オブジェクトのインスタンスを生成してオブジェクト変数「コマンド」に格納します。
5行目～7行目【コネクション.Open ConnectionString:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:¥Users¥Desktop¥Database1.accdb;”】
Connection【ｺﾈｸｼｮﾝ】オブジェクトのOpen【ｵｰﾌﾟﾝ】メソッドを使用して外部データベースの接続をします。
8行目【With コマンド】
Command【ｺﾏﾝﾄﾞ】オブジェクトのインスタンス（複製）が格納されている変数「コマンド」をWith【ｳｨｽﾞ】ステートメントで指定します。
9行目【.ActiveConnection = コネクション】
ActiveConnection【ｱｸﾃｨﾌﾞｺﾈｸｼｮﾝ】プロパティにSQLの対象となるConnection【ｺﾈｸｼｮﾝ】（接続）オブジェクトのインスタンスが格納されているオブジェクト変数「コネクション」
を設定します。
10行目～11行目【.CommandText = _
“SELECT * FROM 商品マスター;”】
CommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティにデータを抽出する、SQLのSELECT【ｾﾚｸﾄ】文を設定します。SELECTにすべてのフィールドを抽出するので「*」ワイルドカードを指定し、FROMに抽出するテーブルを指定します。抽出条件のWHERE【ｳｪｱ】を省略したので指定したフィールドのすべてのレコードが抽出されます。
12行目【Set レコード = .Execute】
指定したSQLを実行するExecute【ｴｸｽｷｭｰﾄ】メソッドを使用してSELECT文を実行し、取得したRecordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトをSet【ｾｯﾄ】ステートメントで指定したRecordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトのインスタンスが格納されているオブジェクト変数「レコード」に代入します。
14行目【Range(“A1”).CopyFromRecordset Data:=レコード】
Range【ﾚﾝｼﾞ】オブジェクトのCopyFromRecordset【ｺﾋﾟｰﾌﾛｰﾑﾚｺｰﾄﾞｾｯﾄ】メソッドで引数DataにSELECT文で抽出したレコードが代入されているオブジェクト変数の「レコード」を指定してA1セルに貼り付けます。
15行目【レコード.Close: Set レコード = Nothing】
Recordset【ﾚｺｰﾄﾞｾｯﾄ】オブジェクトのClose【ｸﾛｰｽﾞ】メソッドでレコードの参照を切断し、Nothing【ﾅｯｼﾝｸﾞ】を代入してメモリー領域を解放します。
16行目【コネクション.Close: Set コネクション = Nothing】
Connection【ｺﾈｸｼｮﾝ】オブジェクトのClose【ｸﾛｰｽﾞ】メソッドで外部データベースの接続を切断し、Nothing【ﾅｯｼﾝｸﾞ】を代入してメモリー領域を解放します。
実行結果
条件をつけてデータを抽出する方法
SELECT【ｾﾚｸﾄ】文で外部データベースのテーブルのレコードを条件をつけて抽出するには
SELECT文のWHERE【ｳｪｱ】に検索文字列を設定します。
検索文字列は「検索フィールド名 比較演算子 検索値」の順で記述します。
検索文字列では以下の比較演算子が使用できます。
| 比較演算子 | 内容 | 使用例 |
|---|---|---|
| < | より小さい | <5 (5より小さい 5は含まない) |
| <= | 以下 | <=5 (5以下 5を含む) |
| > | より大きい | >5 (5より大きい 5は含まない) |
| >= | 以上 | >=5 (5以上 5を含む) |
| = | 等しい | =5 (5と等しい) |
| <> | 等しくない | <>5 (5と等しくない) |
| Like | パターンマッチング | Like ‘山田*’（苗字が山田をすべて) |
※Like【ﾗｲｸ】演算子の場合は「0文字以上の文字列」を表す、ワイルドカードの「*」アスタリスクが文字列の前後に使用できます。
検索値の指定方法は検索フィールドのデータ型によってかわります。
| フィールドのデータ型 | 検索値の指定方法 |
|---|---|
| 文字列型 | 検索値を「’」シングルクォーテーションで囲みます |
| 日付型 | 検索値を「#」シャープ記号で囲みます |
| 数値型 | 検索値をそのまま指定します。 |
検索条件が複数の場合は以下の論理演算子が使用できます。
| 論理演算子 | 書式 | 内容 |
|---|---|---|
| And | 条件1 And 条件2 | 条件1を満たし条件2を満たす |
| Or | 条件1 Or 条件2 | 条件1を満たすか条件2を満たす |
And論理演算子の場合は()で条件をグループ化することができます。
例として
"(ﾌｨｰﾙﾄﾞ='条件1′ And ﾌｨｰﾙﾄﾞ='条件2′) Or (ﾌｨｰﾙﾄﾞ='条件3′ And ﾌｨｰﾙﾄﾞ='条件4′)"
内容は
(条件1を満たし条件2を満たす)または(条件3を満たし条件4を満たす)を表します。
検索値に変数を使用する場合の書式
（例）条件が文字列の場合 ﾌｨｰﾙﾄﾞ名 = ‘”& 変数名 &”‘
（例）条件が数値型の場合 ﾌｨｰﾙﾄﾞ名 > & 変数名
（例）条件が日付型の場合 ﾌｨｰﾙﾄﾞ名 = # “& 変数名 & “#
ユーザーが入力した商品名のデータを抽出するコード例
Sub SELECT文2()
Dim コネクション As New ADODB.Connection
Dim レコード As New ADODB.Recordset
Dim コマンド As New ADODB.Command
Dim 検索値 As String
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
検索値 = InputBox("データを抽出する商品名を入力してください。")
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"SELECT * FROM 商品マスター WHERE 商品名='" & 検索値 & "';"
Set レコード = .Execute
End With
Range("A1").CopyFromRecordset Data:=レコード
レコード.Close: Set レコード = Nothing
コネクション.Close: Set コネクション = Nothing
End If
End Sub
実行結果
以上で、SQLのSELECT文で外部データベースからデータを取得する方法についての解説を終了します。ありがとうございました。