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の「商品マスター」テーブルの
レコードをすべて、ワークシートに抽出する例

コード例

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

ADOのライブラリファイルへの参照設定がされていることが前提のコード例です。

コードの解説

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文で外部データベースからデータを取得する
方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク