photo credit: Highline views via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセル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データベースへの接続を確立するだけのプログラムなので、実際には何も起きません。
流れとしては
- CreateObjectでADODB.Connectionオブジェクトの生成
- OpenメソッドでAccessデータベースへの接続をオープン
- CloseメソッドでAccessデータベースへの接続をクローズ
- ADODB.Connecttionオブジェクトを破棄
という流れですね。
生成して、オープンして、クローズして、破棄…なんか切ない…。
本来であれば2と3の間、オープンしてクローズする間に色々とデータベースとのやり取りをする命令を入れていくことになります。
ADODB.Recordsetオブジェクト
実は、エクセルVBAでデータベースとデータのやり取りをする際に、もう一つADODB関連のオブジェクトを使用する必要があります。
ADODB.Recordsetオブジェクトです。
ただオブジェクトの話をする前に、レコードセット、レコード、フィールドという３つのワードについて解説をしておきます。
レコードセットとは何か？
レコードセットとは何かと言いますと
レコードセットとは、リレーショナルデータベースで、テーブルから何らかの条件で抽出したレコードの集合。
IT用語辞典：レコードセット
ではそのレコードは何かと言いますと
リレーショナルデータベースでは、一件ごとのまとまったデータの並びのことをレコードと呼び、テーブルごとに定められたフィールドの値で構成される。データの記録や削除、参照は原則としてレコード単位で行う。
IT用語辞典：レコード
です。
つまり、エクセルで言うところの行がレコード、列がフィールド、行の集まりをレコードセットということになりますね。
ADODB.Recordsetオブジェクトとは
ADODB.Recordsetオブジェクトはそのレコードセットを格納するためのオブジェクトです。
ADODB.Recordsetオブジェクトを生成する場合は、
とします。
データベースからデータを取得する場合は、ADODB.Connecttionオブジェクトで確立をした接続を通して、何らかの方法でADODB.Recordsetオブジェクトに目的のレコードセットを格納するという流れになります。
その「何らかの方法」が何か、ということについて以降で説明をしていきます。
ADODB.RecordsetオブジェクトにSQL文でレコードセットを格納する
ADODB.Recordsetオブジェクトにレコードセットを格納するには、Openメソッドを使って
とします。
例えば、それぞれの変数名が
- 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文を使います。
書き方はこうです。
この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メソッドは、レコードセットオブジェクトの内容を指定の範囲に書き出すメソッドです。
今回は1番目のシートのA1セルの位置に出力をしています。
実行結果
このプログラムを実行すると、Sheet1に以下のようにデータが出力されるはずです。
ようやくデータベースを操作した感じが出てきましたね。
まとめ
エクセルVBAでAccessデータベースからSQLでデータを抽出する最も簡単なプログラムについてお伝えしました。
ポイントはADODB.Recordsetオブジェクトの使い方とSQL文SELECTによるレコードセットの抽出の仕方ですね。
今回はテーブル全てのデータを取得しましたが、SQL文を変更すれば様々な抽出の仕方をしてレコードセットに格納をすることができます。
逆に言えば、SQL以外のプログラムはほとんどいじる必要がありません。
ということで、次回では様々なレコードセットの抽出の仕方について解説をしたいと思います。
どうぞお楽しみに！
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する
エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。- なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
- エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
- 【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム
- 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
- 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
- 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
- エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
- 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
- エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
- エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
- エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
- エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
- エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
- エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
- エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
