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

★気に入ったらシェアをお願いします!


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メソッドを使って並び替えをする方法についてお伝えします。

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

お題のデータベース

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

Accessのデータベース

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

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

です。

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

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

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

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

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

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

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

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

ADODBレコードセットをシートに出力

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

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

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

また、F1セルに

=COUNTIF(F2:F9001,”NG”)

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

結果はこちらです。

ADODBレコードセットのIDが連続でない箇所

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行目を以下のようにします。

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

ADODBレコードセットをSortメソッドで並び替えをした

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

まとめ

ADODBレコードセットもその並び順に関しては法則はありません。またADODBレコードセットを並び替えをするSortメソッドの使い方についてお伝えしました。(CursorLocationプロパティも忘れずに。)

したがって、以下の記事のように、例えば大量のレコードの更新をFindメソッド、Updateメソッドで行いたい、かつ速度を犠牲にしないためにMoveFirstメソッドを使わない場合は、今回の方法と前回の方法を組み合わせることによって実現が可能です。

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

これで一件落着です。

次回はまた少し視点を変えて、SELECTの際に集計をしながら出力するGROUP BY句の使い方と集計関数についてお伝えします。

エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。今回はデータベースのレコードをグループで集計して抽出する方法です。SQL文のGROUP BY句と集計関数を使いますよ。

どうぞお楽しみに!

連載目次:エクセル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データベースの複数のレコードを上書き更新する場合の実行速度について
  13. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  14. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  15. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  16. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  17. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
  18. エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる

The following two tabs change content below.
1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、コンサル、執筆本を中心に活動しています。→詳しいプロフィールはコチラ ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ ★フォロー頂ければ嬉しいです。