Excel VBA SQLのSELECT文で外部データベースからデータを取得する

スポンサーリンク

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
2行目【Dim コネクション As New ADODB.Connection】
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文で外部データベースからデータを取得する方法についての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告