エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す

photo credit: thierry llansades Congost de Mont Rebei via photopin (license)

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

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

前回の記事はコチラ。

エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について

エクセルVBAでAccessデータベースを操作する方法について、今回はADODBレコードセットのFind、Update、MoveFirstを使って更新する方法について、またその実行速度についてです。

Accessデータベースの複数のレコードをまとめて上書き更新する方法についてその実行速度を検証しました。

それで、その際にSQLのSELECT文で取得してエクセルシートに取り出したデータも、レコードセットのレコードもIDで昇順になっていない可能性があるのでは？

という疑問が出てきたんですね。

今回はその検証の第一弾として、SQLのSELECT文で取得したデータの順番がどうなっているのかどうかを検証します。また同時に、SELECT文で取得する際に並び替えをしながら取得するORDER BY句の使い方についてお伝えをします。

お題のデータベース
SQLのSELECT文でテーブルを抽出する
SELECT文で抽出結果の並び順
ORDER BY句で並び替えをして抽出する
実行結果
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

お題のデータベース

今回もこちらのデータベースを使います。

ファイル名は「test4.accdb」、テーブル名は「成績表」、フィールドの構成は

｜ID｜名前｜国語｜数学｜英語｜

です。

SQLのSELECT文でテーブルを抽出する

では、まずSQLのSELECT文でテーブル「成績表」をゴソっととってきた場合の並び順を確認してみます。

こちらのプログラムで、Sheet3にゴソっと貼り付けます。

Sub fetchRecords()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")

Dim strFileName As String
strFileName = "test4.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へ
ws.Range("A2").CopyFromRecordset adoRs

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

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

End Sub

このプログラムは以下記事で紹介していますね。

エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法

エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。今回はSQLのUPDATE文を使ってAccessデータベースの複数レコードをまとめて上書き更新する方法についてお伝えします。

SELECT文で抽出結果の並び順

では、こちらのプログラムで出力したデータの並び順がどうなのかを見てみましょう。

出力したデータはこのようになっています。

F列に例えばF3セルであれば

=IF(A3=A2+1,””,”NG”)

という数式を入れて、IDが前の行のプラス1でない行に「NG」と表示されるようにしました。また、F1セルに

=COUNTIF(F2:F9001,”NG”)

として「NG」の表示数をカウントします。

結果はこちら。

順番に並んでいない箇所が5箇所あります。

いずれの「NG」の箇所も確認する限りは法則性はありませんでした。

ですから、単に

SELECT * FROM テーブル名

で抽出した場合には、IDでの並び順は全くの法則性も求めてはいけないということになります。

ORDER BY句で並び替えをして抽出する

SELECTでデータを抽出する際に並び替えをする場合は、ORDER BY句を使います。

書き方は

SELECT フィールド名 FROM テーブル名 ORDER BY フィールド名1

と書きます。

ORDER BYの後に、並び替えの対象とするフィールド名を記載します。ASC/DESCはそれぞれ昇順、降順を表しますので、どちらかを記載します。

ASC：昇順
DESC：降順

また、複数のフィールドで組み合わせて並び替えをすることもできます。

SELECT フィールド名 FROM テーブル名 ORDER BY フィールド名1 ASC/DESC, フィールド名2 ASC/DESC, …

この場合、先に書いた順に並び替えになりますので、フィールド1でソートされた後に、フィールド2でソートされます。

前述のプログラムのSQL文を生成している箇所である17行目を

strSQL = "SELECT * FROM 成績表 ORDER BY ID ASC"

とすればOKですね。

実行結果

では、SQL文にORDER BY句を加えた上でプログラムを実行してみましょう。

結果はこちらです。

無事にNGがなくなりましたね。

まとめ

検証した通り、特に指定をしない限りはSQLのSELECT文で取得したデータの順番は全くのでたらめとなっちゃいます。

そして、データの順番を整列して取り出したい場合は、SELECT文にORDER BY句を使うことになります。

ぜひご活用くださいね。

今回、SQLのSELECT文で抽出したデータの並び順について明らかになりましたので、次はレコードセットのデータの並び順について検証をしていきます。

エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。ADODBレコードセットの並び順について確認しつつ、Sortメソッドを使って並び替えをする方法についてです。

どうぞお楽しみに！

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

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。