【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。

前回はコチラの記事。

エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
エクセルVBAからAccessデータベースを操作する方法についてシリーズでお伝えしています。今回は実際にエクセルVBAからAccessデータベースに接続をして切断をする超簡単プログラムを紹介します。

ADODB.Connectionオブジェクトを使ってエクセルVBAからAccessデータベースに接続する方法についてお伝えしました。

接続しただけで何もできていませんので、今回はいよいよAccessデータベースからデータを抽出してきたいと思います。

その際にSQLというデータベース操作のための言語と、ADODB.Recordsetオブジェクトを使いますが、その最も簡単な例について示していきたいと思います。

エクセルVBAでAccessデータベースからSQLでデータを抽出する最も簡単なプログラム、行ってみましょう!

スポンサーリンク

前回のおさらい:Accessデータベースへの接続

前回の記事では以下のようなプログラムを作成しました。

Sub ConnectDB()

Dim strFileName As String
strFileName = "test.accdb" 'データベースのファイル名

Dim adoCn As Object
Set adoCn = CreateObject("ADODB.Connection") 'ADODBコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

adoCn.Close 'コネクションのクローズ
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

ただAccessデータベースへの接続を確立するだけのプログラムなので、実際には何も起きません。

流れとしては

  1. CreateObjectでADODB.Connectionオブジェクトの生成
  2. OpenメソッドでAccessデータベースへの接続をオープン
  3. CloseメソッドでAccessデータベースへの接続をクローズ
  4. ADODB.Connecttionオブジェクトを破棄

という流れですね。

生成して、オープンして、クローズして、破棄…なんか切ない…。

本来であれば2と3の間、オープンしてクローズする間に色々とデータベースとのやり取りをする命令を入れていくことになります。

ADODB.Recordsetオブジェクト

実は、エクセルVBAでデータベースとデータのやり取りをする際に、もう一つADODB関連のオブジェクトを使用する必要があります。

ADODB.Recordsetオブジェクトです。

ただオブジェクトの話をする前に、レコードセット、レコード、フィールドという3つのワードについて解説をしておきます。

レコードセットとは何か?

レコードセットとは何かと言いますと

レコードセットとは、リレーショナルデータベースで、テーブルから何らかの条件で抽出したレコードの集合。
IT用語辞典:レコードセット

ではそのレコードは何かと言いますと

リレーショナルデータベースでは、一件ごとのまとまったデータの並びのことをレコードと呼び、テーブルごとに定められたフィールドの値で構成される。データの記録や削除、参照は原則としてレコード単位で行う。
IT用語辞典:レコード

です。

つまり、エクセルで言うところの行がレコード、列がフィールド、行の集まりをレコードセットということになりますね。

レコード、フィールド、レコードセット

ADODB.Recordsetオブジェクトとは

ADODB.Recordsetオブジェクトはそのレコードセットを格納するためのオブジェクトです。

ADODB.Recordsetオブジェクトを生成する場合は、

Set オブジェクト変数 = CreateObject(“ADODB.Recordset”)

とします。

データベースからデータを取得する場合は、ADODB.Connecttionオブジェクトで確立をした接続を通して、何らかの方法でADODB.Recordsetオブジェクトに目的のレコードセットを格納するという流れになります。

その「何らかの方法」が何か、ということについて以降で説明をしていきます。

ADODB.RecordsetオブジェクトにSQL文でレコードセットを格納する

ADODB.Recordsetオブジェクトにレコードセットを格納するには、Openメソッドを使って

ADODBレコードセットオブジェクト.Open SQL文, ADODBコネクションオブジェクト

とします。

例えば、それぞれの変数名が

  • adoRs:ADODB.Recordsetオブジェクト
  • adoCn:ADODB.Connecttionオブジェクト
  • strSQL:SQL文

であった場合は

adoRs.Open strSQL, adoCn

と書きます。

SQLとは何か

SQLとは何か、ということですが

SQL(Structured Query Language)はDBMSへ利用者や外部のソフトウェアから命令を発行するために用いる言語で、データベースへのテーブルの追加や設定変更、削除、テーブル間の関係の定義や削除、テーブルへのデータの追加、更新、削除、データベースやシステムの設定変更などを行うための命令語と構文、文法などを定めている。
IT用語辞典:SQL

です。データベースに様々な操作を行うための言語です。

なお、SQLは業界標準規格ですのでAccessデータベースに限らず、MySQLやOracle Databaseでも同様に使用することができます。

主な命令としては例えば

  • SELECT:データを抽出する
  • INSERT:テーブルにデータを追加する
  • DELETE:テーブルからデータを削除する
  • UPDATE:テーブルのデータを更新する

などといったものがあります。

SELECTでデータベースからデータを抽出する

今回はデータベースからデータを抽出するという目的ですので、SELECTによるSQL文を使います。

書き方はこうです。

SELECT フィールド名 FROM テーブル名

このSQL文で指定したテーブル名から、指定したフィールドを取り出してきます。

例として接続したデータベースから「データ」という名前のテーブルのレコードをごっそり全て抽出して、ADODB.Recordsetオブジェクトに格納をしてみたいと思います。

その場合のSQL文は

SELECT * FROM データ

となります。

フィールド名に「*」を指定すると、全てのフィールドを対象とします。

このSQL文を文字列型の変数に格納して、前述のADODB.RecordsetオブジェクトのOpenメソッドを使えばOKです。

テーブルのデータ全てをシートに書き出すプログラム

AccessデータベースからSQLでテーブルのデータを全て抽出してシートに書き出すプログラムはこちらです。

Sub SelectDB()

Dim strFileName As String
strFileName = "test.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

Dim strSQL As String
strSQL = "SELECT * FROM データ"

adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
Worksheets(1).Range("A1").CopyFromRecordset adoRs

adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

8行目のADODB.Connecttionオブジェクトをオープンして接続を確立するまでは、前回のプログラムと同様です。

11行目でADODB.Recordsetオブジェクトを生成しています。

14行目でSQL文を変数に格納し、16行目でそのSQL文を実行した結果のレコードセットを取得しています。

ADODB.Recordsetオブジェクトも使い終わったら、クローズとオブジェクトの破棄をします。その箇所が19行目と22行目ですね。

CopyFromRecordsetでレコードセットをシートに書き出す

17行目のCopyFromRecordsetメソッドは、レコードセットオブジェクトの内容を指定の範囲に書き出すメソッドです。

Rangeオブジェクト.CopyFromRecordset ADODBレコードセットオブジェクト

今回は1番目のシートのA1セルの位置に出力をしています。

実行結果

このプログラムを実行すると、Sheet1に以下のようにデータが出力されるはずです。

Accessから取得したテーブルのデータ

ようやくデータベースを操作した感じが出てきましたね。

まとめ

エクセルVBAでAccessデータベースからSQLでデータを抽出する最も簡単なプログラムについてお伝えしました。

ポイントはADODB.Recordsetオブジェクトの使い方SQL文SELECTによるレコードセットの抽出の仕方ですね。

今回はテーブル全てのデータを取得しましたが、SQL文を変更すれば様々な抽出の仕方をしてレコードセットに格納をすることができます。

逆に言えば、SQL以外のプログラムはほとんどいじる必要がありません。

ということで、次回では様々なレコードセットの抽出の仕方について解説をしたいと思います。

【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
エクセルVBAでAccessデータベースを操作するシリーズ。今回はAccessデータベースのテーブルから色々なパターンでデータを抽出するSELECT、WHEREの使い方についてお伝えします。

どうぞお楽しみに!

連載目次:エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。
  1. なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
  2. エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
  3. 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
  4. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
  5. 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
  6. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  7. エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
  8. 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
  9. エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
  10. エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
  11. エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
  12. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  13. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  14. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  15. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  16. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
  17. エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
タイトルとURLをコピーしました