ADOでデータを取得する。

ADOOLEDBとも称されるデータベースへの新しい接続方法です。

Accessのテーブルから取り込んだところ
※この画像をクリックするとZIP圧縮ファイルがダウンロードされます。解凍すると「F-1ポイント計算.xls」と「DBフォルダ」が現われるので、そのまま同じフォルダにコピーさせて下さい。

ダウンロードしたらどこかのフォルダに内容をそのまままとめて解凍し、「F-1ポイント計算.xls」を開いて下さい。
マクロを起動させる。
このようにマクロを起動させて下さい。(特に起動ボタンは用意していません。)
マクロを選択して実行をクリック!
このようにマクロ名の選択画面が表示されるので、希望するマクロ名をクリックで反転させて「実行」をクリックします。ここでは「TEST5」を選択して下さい。
先頭画面のような結果になりましたか?
(前提条件となる環境が整っていないとエラーになる場合もあります。)

では、コード内容を見てみましょう。


'*******************************************************************************
'   ADOでAccessデータベースからデータをシート上に展開する
'
'   作成者:井上治  URL:http://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'*******************************************************************************
Option Explicit
Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Const cnsADO_CONNECT2 = "\DB\SAMPLE.mdb;"

'*******************************************************************************
'   ADOでフィールドを個々に指定して読み込む
'*******************************************************************************
Sub TEST5()
    ' 参照設定「Microsoft Active Data Object 2.x Library」
    Dim dbCon As New ADODB.Connection
    Dim dbRes As New ADODB.Recordset
    Dim dbCols As ADODB.Fields
    Dim strSQL As String
    Dim GYO As Long

    ' 画面描画更新停止
    Call GP_StopSCUPD
    Worksheets("Sheet1").Activate
    ' 接続を確立する
    dbCon.Open cnsADO_CONNECT1 & ThisWorkbook.Path & cnsADO_CONNECT2
    ' テーブル名,条件を指定してレコードセットを取得する
    strSQL = "SELECT * FROM GP開催マスタ WHERE 西暦年=2004 ORDER BY 開催順SEQ"
    dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly
    GYO = 1
    Rows("2:65536").ClearContents
    ' 先頭レコードからEOFまで繰り返す
    dbRes.MoveFirst                     ' ←この行はなくても問題なし
    Do Until dbRes.EOF
        ' 行の変数を加算し必要項目を選択してセルにセット
        GYO = GYO + 1
        Set dbCols = dbRes.Fields
        Cells(GYO, 1).Value = dbCols("西暦年").Value
        Cells(GYO, 2).Value = dbCols("開催順SEQ").Value
        Cells(GYO, 3).Value = dbCols("決勝開催日").Value
        Cells(GYO, 4).Value = dbCols("GP名").Value
        Cells(GYO, 5).Value = dbCols("開催地").Value
        ' 次のレコードに移る
        dbRes.MoveNext
    Loop
    ' レコードセット、データベースを閉じる
    dbRes.Close
    Set dbRes = Nothing
    dbCon.Close
    Set dbCon = Nothing
    ' 画面描画更新復帰
    Call GP_StartSCUPD
End Sub

このように、セルにフィールド内容をセットする所は個々に記述しているので、フィールドの選択や順番、またセル位置などは自由自在に設定できます。

一方、フィールドを個々に選択しない方法としては、

'*******************************************************************************
'   ADOで全フィールドを登録順に読み込む
'*******************************************************************************
Sub TEST6()
    Dim dbCon As New ADODB.Connection
    Dim dbRes As New ADODB.Recordset
    Dim dbCol As ADODB.Field
    Dim strSQL As String
    Dim GYO As Long
    Dim COL As Long
    Dim strNENDO As String

    ' 画面描画更新停止
    Call GP_StopSCUPD
    Worksheets("Sheet1").Activate
    ' 接続を確立する
    dbCon.Open cnsADO_CONNECT1 & ThisWorkbook.Path & cnsADO_CONNECT2

    ' 年度の設定(A2セル)
    If Cells(2, 1).Value <> "" Then
        strNENDO = Cells(2, 1).Value
    Else
        strNENDO = cnsNENDO
    End If
    ' テーブル名,条件を指定してレコードセットを取得する
    strSQL = "SELECT * FROM GP開催マスタ WHERE 西暦年=" & strNENDO & _
        " ORDER BY 開催順SEQ"
    dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly
    GYO = 1
    Rows("2:65536").ClearContents
    ' 先頭レコードからEOFまで繰り返す
    dbRes.MoveFirst                     ' ←この行はなくても問題なし
    Do Until dbRes.EOF
        ' 行の変数を加算
        GYO = GYO + 1
        ' テーブルの登録全フールドをセルにセット
        COL = 0
        For Each dbCol In dbRes.Fields
            COL = COL + 1
            Cells(GYO, COL).Value = dbCol.Value
        Next dbCol
        ' 次のレコードに移る
        dbRes.MoveNext
    Loop
    ' レコードセット、データベースを閉じる
    dbRes.Close
    Set dbRes = Nothing
    dbCon.Close
    Set dbCon = Nothing
    ' 画面描画更新復帰
    Call GP_StartSCUPD
End Sub
このような方法があります。これだとフィールド名を意識することなく、登録順に並べて表示されます。
テーブルにフィールドを追加してもコードを変更することなく追加されたフィールドが右端に追加されて表示されます。

さらに、もっと「無精」な方法になりますが、上記同様にレコードセットの項目を全て並び順通りにセルに配置できる前提となりますが、

'*******************************************************************************
'   ADOで全フィールドを登録順に読み込む
'*******************************************************************************
Sub TEST6_2()
    Dim dbCon As New ADODB.Connection
    Dim dbRes As New ADODB.Recordset
    Dim dbCol As ADODB.Field
    Dim strSQL As String
    Dim strNENDO As String

    ' 画面描画更新停止
    Call GP_StopSCUPD
    Worksheets("Sheet1").Activate
    ' 接続を確立する
    dbCon.Open cnsADO_CONNECT1 & ThisWorkbook.Path & cnsADO_CONNECT2

    ' 年度の設定(A2セル)
    If Cells(2, 1).Value <> "" Then
        strNENDO = Cells(2, 1).Value
    Else
        strNENDO = cnsNENDO
    End If
    ' テーブル名,条件を指定してレコードセットを取得する
    strSQL = "SELECT * FROM GP開催マスタ WHERE 西暦年=" & strNENDO & _
        " ORDER BY 開催順SEQ"
    dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly
    Rows("2:65536").ClearContents
    ' レコードセットからまとめて転記する
    Range("A2").CopyFromRecordset dbRes
    ' レコードセット、データベースを閉じる
    dbRes.Close
    Set dbRes = Nothing
    dbCon.Close
    Set dbCon = Nothing
    ' 画面描画更新復帰
    Call GP_StartSCUPD
End Sub

'-----------------------------<< End of Source >>-------------------------------
このように左上の起点となるセルを指定して独自にループ処理を書かずに済ませる方法もあります。

上記のコードの内、「画面描画更新停止」「画面描画更新復帰」は「ZZZ_Module」に共通処理として置いています。(DAOでデータを取得する。」の中で説明しています。)

※先頭のコメントに「参照設定:Microsoft Active Data Object 2.x Library」と説明してありますが、このサンプルコードのようにADOに関するデータ型を明示する場合は、ADOを参照設定する必要があります。
VBE上の「ツール」メニューの「参照設定」で、
参照設定でADOのチェックを付ける。
このようにADOにチェックを付けて下さい。
ADOには複数のバージョンが存在するため、配布目的の場合には配布先とバージョンが異なる場合にはこの
参照設定の画面に「参照不可」と表示される場合があります。
そのような場合は、「参照不可」の項目のチェックを外し、実際に存在するバージョンのADOにチェックを付けてから上書き保存させれば済むはずです。
実行時に「CreateObject」を使ってバインドし、「参照設定」自体を使わない方法はありますが、配布先の環境が多種多様でない場合は必要ありません。
実行時バインドを使う場合でも、最初はこのように「参照設定」を使って十分に動作を確認した上で切り替えるようにして下さい。

※参考資料ですが、Excelを複数バージョンインストールし、MDACも複数バージョンインストールしている私の環境では、「参照設定」にADOはこのように複数バージョンが登録されています。
ADOにはこのように複数バージョンが存在する。
この「MDAC」自身は、マイクロソフトのサイトからダウンロードできますから、このサンプルがうまく動かない場合は最新版をダウンロードしてインストールしてみてください。

ADOでデータを取得する(実行時バインド版)。

この前の説明でも触れましたが、配布先のMDACのバージョンがそろっていないようなケースでは「実行時バインド」という手があります。
上記のサンプルのように参照設定を行なわず、「CreateObject関数」を使って「ADODB.Connection」「ADODB.Recordset」等のオブジェクトを実行時に生成する方法です。 おそらく、参照設定の方法より実行時にオブジェクトを生成する分が若干遅くなる要因にはなるのでしょうが、このページの最後のコラムで紹介しているASP関連」では参照設定が使えないのですべてこの「実行時バインド」によって動作しますから、この方法でも充分だと思います。
コード中の違う部分にはコメントを入れておきました。 ここをクリックすると、参照設定版を含めたサンプルがダウンロードできます。


'*******************************************************************************
'   ADOでAccessデータベースからデータをシート上に展開する(非参照設定版)
'
'   作成者:井上治  URL:http://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'*******************************************************************************
Option Explicit
Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Const cnsADO_CONNECT2 = "\DB\SAMPLE.mdb;"
Const cnsNENDO = 2006                           ' 年指定がない時の年度
'**********↓↓↓非参照設定版はこれを追加します↓↓↓**********
Const adOpenKeyset = 1
Const adLockReadOnly = 1
'**********↑↑↑非参照設定版はこれを追加します↑↑↑**********

'*******************************************************************************
'   ADOでフィールドを個々に指定して読み込む
'*******************************************************************************
Sub TEST5()
'**********↓↓↓参照設定版とはここが違います↓↓↓**********
'    Dim dbCon As New ADODB.Connection
'    Dim dbRes As New ADODB.Recordset
'    Dim dbCols As ADODB.Fields
    Dim dbCon As Object
    Dim dbRes As Object
    Dim dbCols As Object
'**********↑↑↑参照設定版とはここが違います↑↑↑**********
    Dim strSQL As String
    Dim GYO As Long
    Dim strNEN As String

    ' 画面描画更新停止
    Call GP_StopSCUPD
    Worksheets("Sheet1").Activate
    ' 接続を確立する
'**********↓↓↓非参照設定版はこれを追加します↓↓↓**********
    Set dbCon = CreateObject("ADODB.Connection")
'**********↑↑↑非参照設定版はこれを追加します↑↑↑**********
    dbCon.Open cnsADO_CONNECT1 & ThisWorkbook.Path & cnsADO_CONNECT2

    ' 年度の設定(A2セルから取得)
    If Range("A2").Value <> "" Then
        strNEN = Range("A2").Value
    Else
        strNEN = cnsNENDO
    End If
    ' テーブル名,条件を指定してレコードセットを取得する
    strSQL = "SELECT * FROM GP開催マスタ WHERE 西暦年=" & strNEN & _
        " ORDER BY 開催順SEQ"
'**********↓↓↓非参照設定版はこれを追加します↓↓↓**********
    Set dbRes = CreateObject("ADODB.Recordset")
'**********↑↑↑非参照設定版はこれを追加します↑↑↑**********
    dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly
    GYO = 1
    Rows("2:65536").ClearContents
    ' 先頭レコードからEOFまで繰り返す
    dbRes.MoveFirst                     ' ←この行はなくても問題なし
    Do Until dbRes.EOF
        ' 行の変数を加算し必要項目を選択してセルにセット
        GYO = GYO + 1
        Set dbCols = dbRes.Fields
        Cells(GYO, 1).Value = dbCols("西暦年").Value
        Cells(GYO, 2).Value = dbCols("開催順SEQ").Value
        Cells(GYO, 3).Value = dbCols("決勝開催日").Value
        Cells(GYO, 4).Value = dbCols("GP名").Value
        Cells(GYO, 5).Value = dbCols("開催地").Value
        ' 次のレコードに移る
        dbRes.MoveNext
    Loop
    ' レコードセット、データベースを閉じる
    dbRes.Close
    Set dbRes = Nothing
    dbCon.Close
    Set dbCon = Nothing
    ' 画面描画更新復帰
    Call GP_StartSCUPD
End Sub
この方法では、「dbCon」「dbRes」などはすべて「Object」で宣言されています。(実際は「Variant」でも構いません。) つまり、「Setステートメント」でオブジェクトの参照を与えられるまでは「何だか分からない」ことになります。 ですから、コードの作成ではプロパティの自動メンバ表示もされないし、コンパイルでの不具合摘出もできません。また、ライブラリが提供する名前付き定数も利用できないので、ここでも「adOpenKeyset」「adLockReadOnly」はコード内で定数宣言させています。
これらのことから、最終的に「実行時バインド」の方法を採用するとしても、マクロ作成の段階では一旦、「参照設定」を行なってやる方がはるかに効率的だといえると思います。いきなり「実行時バインド」で記述して動かせるのはかなりのベテランでなければならないでしょう。 ASP関連」の方法では逆に「実行時バインド」しか使えないので、このことを逆手に取ってExcelVBAでプロシージャ単位の作成・テストを行なうことをお勧めしています。
このページの最初の画像をクリックしてダウンロードできる動作サンプルには、「F-1ポイント計算(非参照設定版).xls」も入れてありますから、動かしてみることもできます。

Accessを持たない環境でMDBを使いたい方へ   以下のようなツールをご用意していますので、合わせてご利用下さい。
MDB生成/テーブル定義取得ツール」
  ⇒ワークシート上に登録したテーブル定義内容で実際にMDB(データベース)ファイルを作成したり、
    現存するMDB(データベース)ファイルの定義内容を取得するツールです。
MDBデータ取得ツール」
  ⇒SQL文の検証や、データの調査・修正を行なうツールです。
データベースの共有利用の目的でこのページをお読みの方へ   MicrosoftASPをご存じでしょうか。
MicrosoftWebサーバサービスであるIIS上で動作するアプリケーションのベースとなる機能です。
Excelでお仕事!」でも、このASPを取り上げており、このページの題材をInternet Explorer上で動くアプリケーションに仕立てたサンプルを作ってあります。 関心がある方は、ASP関連」の各ページをご覧下さい。