VBAでAccessデータベースを活用しよう～全データ取得編～
前回の記事ではAccessデータベースへのデータ追加と削除を行いました。
今回はいよいよ入力されたデータの取得をやっていきます。
前回は以下のデータを入力しました。
| ID | 名前 | 生年月日 | 年齢 | 職業 |
|---|---|---|---|---|
| 1 | サザエ | 1922/11/22 | 27 | 主婦 |
| 2 | 波平 | 1985/9/14 | 54 | 会社員 |
| 3 | フネ | 1901/1/11 | 48 | 主婦 |
| 4 | マスオ | 1971/4/3 | 32 | 会社員 |
| 5 | カツオ | 1938/3/11 | 11 | 小学生 |
| 6 | ワカメ | 1942/6/15 | 9 | 小学生 |
| 7 | タラオ | 1947/3/18 | 3 | 子ども |
今回はこのデータベースに入力されたデータを取り出していきたいと思います。
それでは行ってみましょう！
全データの取得
Recordsetオブジェクトでデータを取得しよう
データベース中のデータの取得にはRecordsetオブジェクトを使用します。
取得データには様々な条件を設定することができます。
全データの取得、特定の値を持ったデータの取得、ある範囲に値を持つデータの取得、値が大きい順に並び替えしてデータ取得等々・・・
応用範囲が広くてきりがないほど、条件の自由度はかなり高いです。
その条件には次のSQL文を使っていきます。
SQL文を使おう
条件の設定にはSQL文を使います。
オブジェクトを使用することもできるのですが、SQL文の方が汎用性があっていいかなと思います。
全データを取得する条件のSQL文は次のようになります。
SQL="SELECT * FROM テーブル名"
「*」を使うことで全列のデータを取得できます。
コードを書いていこう
それでは全データを取得するコードを書いていきましょう。
次のコードは取得したデータを二次元配列に入れています。
データは縦と横に広がってますから二次元配列にしました。
また、エクセル上で表示できるように二次元配列からデータを取得しています。
配列に入れた方がマクロの組み合わせがやりやすいので。
Public Sub 全データ取得() '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String Dim SQL As String Dim AryData() as Variant Dim i as Long Dim j as Long DBFile = "ファイルの絶対パス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile SQL = "SELECT * FROM テーブル名" Set CN = New ADODB.Connection CN.ConnectionString = strDB CN.Open Set RS = New ADODB.Recordset RS.Open Source:=SQL, ActiveConnection:=CN RS.MoveFirst i = 1 Do Until RS.EOF ReDim Preserve AryData(RS.Fields.Count - 1, i) For j = 0 To RS.Fields.Count - 1 AryData(j, 0) = RS(j).Name AryData(j, i) = RS(j) Next j i = i + 1 RS.MoveNext Loop RS.Close CN.Close Set RS = Nothing Set CN = Nothing On Error Resume Next For i = 0 To UBound(AryData, 2) For j = 0 To UBound(AryData, 1) Cells(i + 1, j + 1) = AryData(j, i) Next j Next i End Sub
★コードの解説★
コードの解説を簡単にしていきます。
まず、10行目までは変数宣言です。
全データを取得するための登場人物がここに並んでいます。
12~13行目はデータベースへの接続先が書いてあります。
15行目が今回の主役のSQL文ですね。
これで全データの取得ができます。
17~21行目はデータベースへの接続と、SQLで書いた条件のデータ取得を行っています。
23行目からは取得したデータを配列に入れています。
ここからが注意です。
実はデータは1行ずつしか取得できません。
行を移動するにはMoveNextを使います。
最初の行に移動するにはMoveFirstを使います。
全データの取得にはMoveFirstで先頭の行に移動させてからMoveNextで行を最終行まで移動させます。
最終行になったらEOF(End of File)という値がTrueになるので、EOFがTrueになるまでDo〜Loopを繰り返します。
40行目以降は配列に入れたデータをエクセル上に書き出す処理です。
ここまでで全データを取得するコードが書けました。
★波平の逆襲★
40行目にエラー回避コマンドを入れています。
これは本来必要な処理ではないのですが、エクセルの仕様として日付は1900年から処理できるんですね。
なので波平さんはエクセル上では日付として処理できないわけです。
上のコードを実行すると波平だけ空白になると思います。
下のコードは1900年を下回る場合は日付型でなく文字列でセルに書き出すコードです。
40行目以降を以下のコードに置き換えると波平の生年月日も表示されます。
配列の中が日付型のときにシリアル値が1以下であれば文字列にするようにしてあります。
For i = 0 To UBound(myAry, 1) For j = 0 To UBound(myAry, 2) If VarType(myAry(i, j)) = vbDate Then If CDbl(myAry(i, j)) < 1 Then Cells(j + 1, i + 1) = Format(myAry(i, j), "yyyy/mm/dd") Else Cells(j + 1, i + 1) = myAry(i, j) End If Else Cells(j + 1, i + 1) = myAry(i, j) End If Next j Next i
