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

photo credit: markus spiske fresh bio pepper via photopin (license)

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

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

前回はコチラの記事です。

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

エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。今回はSQLのSELECT文で取得したデータの順番についての検証、並び替えて取得するORDER BY句の使い方についてです。

SQLのSELECT文で取得したデータの並び順について確認しつつ、並び替えをしながら取得するORDER BY句の使い方について解説をしました。

今回ですが、そのADODBレコードセット版ですね。

Accessデータベースのテーブルを指定してADODBレコードセットをオープンした場合の並び順について確認しつつ、Sortメソッドを使って並び替えをする方法についてお伝えします。

では、行ってみましょう。

お題のデータベース
ADODBレコードセットをシートに書き出す
ADODBレコードセットの並び順
Sortメソッドでレコードセットを並び替えする
1. CursorLocationプロパティを変更する
プログラムを実行する
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

お題のデータベース

毎度で恐縮ですが、今回使うのもこのデータベースです。

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

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

です。

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

ADODBレコードセットオブジェクトに取得をして、そこからエクセルのSheet4に書き出してIDの並び順を確認してみましょう。

プログラムはコチラです。

Sub fetchRecords_byADO()

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レコードセットオブジェクトを作成

With adoRs
    .Open "成績表", adoCn 'レコードセットを開く
    Worksheets("Sheet4").Range("A2").CopyFromRecordset adoRs
    .Close    'レコードセットのクローズ
End With

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

End Sub

14行目でテーブル「成績表」を指定してレコードセットを開きます。これでレコードセットの中に成績表がゴソっと入ったというイメージです。

15行目のCopyFromRecordsetメソッドで、それをそのままSheet4のA2セルを先頭として貼り付けます。

ADODBレコードセットの並び順

このプログラムで出力したデータの並び順を確認してみましょう。

Sheet4の出力はこのようになっています。

前回同様に、例えばセルF3に

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

として、それをF列のレコードの最後までコピーします。

また、F1セルに

=COUNTIF(F2:F9001,”NG”)

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

結果はこちらです。

IDが連続していない箇所が5箇所ありました。…前回のSQLのSELECT文の場合と同じです。調べたところ、NGがついている行数も一緒でした。

ADODBレコードセットも、レコードの並び順に関しては特に法則性はないということになりますね。

Sortメソッドでレコードセットを並び替えする

ADODBレコードセットのレコードを並び替えをする際は、Sortメソッドを使います。

書き方は

ADODBレコードセットオブジェクト.Sort “フィールド名 ASC/DESC”

です。対象とするフィールド名とASCまたはDESCを半角で記載し、まとめてダブルクォーテーションで囲みます。

昇順にしたい場合はASC、降順にしたいときはDESCですね。

複数のフィールドをキーに並び替えをしたい場合は

ADODBレコードセットオブジェクト.Sort “フィールド名1 ASC/DESC, フィールド名2 ASC/DESC, …”

とカンマ区切りにします。先に書いた順に並び替えをしますので、フィールド1でソートをした後に、フィールド2でソートされます。

CursorLocationプロパティを変更する

なお、Sortメソッドですが、ADODBレコードセットをオープンする前にCursorLocationプロパティを「adUseClient」に変更する必要があります。（デフォルトでは「adUseServer」に指定されています。

ADODBレコードセットオブジェクト.CursorLocation = adUseClient

Sortメソッドを使う際は、忘れずに設定をしておきましょう。

プログラムを実行する

以上を踏まえて、プログラムの13行目～17行目を以下のようにします。

With adoRs
    .CursorLocation = adUseClient 'Sortをするための設定
    .Open "成績表", adoCn 'レコードセットを開く
    .Sort = "ID ASC"
    Worksheets("Sheet4").Range("A2").CopyFromRecordset adoRs
    .Close    'レコードセットのクローズ
End With

これで実行をしてみましょう。