中級者のためのExcel エクセルマクロVBA：Accessデータベースとつなぐその４ - 初心者のためのExcelマクロ超入門（絶対できるVBA開発）

Accessとつなぐことでエクセルはシステムとして生きる

やっとですが、VBAを使ってエクセルとAccessをつないでみたいと思います。Accessからはもう離れて大丈夫です。必要なのは各テーブルの構成だけで、それがあれば特にAccessを使ってどうこうすることはなく、基本はテーブルの中のデータを確認するだけになります。Accessにデータを登録したり、更新したり、削除したりするのはすべて「Excel」の役目になるわけです。そうVBAならね。

エクセルだけならそれはタダの表計算ソフトです。データを100万件程度まで貯めることもできますが、実際100万行もあるエクセルファイルって開いたことありますでしょうか？超！！超！！重いです。開くだけで数秒かかるし、データをいじるのも動作が遅いので結構大変です。やってみればわかるけど。

だからこそ、Accessが重要で、これをつなぐことでデータ自体の管理はAccessが行い、エクセルはそのデータの操作のみに集中することができるのです。これはもはやVBAではありますが、完全なる「業務システム」です。こんなこともVBAならできるのです。素晴らしいですね。

では、エクセルとAccessをつないでいきましょう。

AccessとExcelをつなぐために必要なこと。

AccessデータベースとExcelとをつなぐにはそれなりに「準備」が必要です。必要な情報をエクセルに記述して、それを使ってAccessにアクセスしていきます。

必要なものは以下になります。

接続文字列
Accessファイルのパス
データベース接続オブジェクト
レコードセットオブジェクト

となります。これらを使ってAccessへ接続して、データを取り出したりするわけです。

ではコードを書いてみます。中級なのでコードいきなり行きます。エクセルシートにボタンを付けて、ボタンを押したら商品の情報がシートに出力されるようにしてみましょう。
まずは上に必要なデータベース接続オブジェクトやレコードセットオブジェクトが使えるように参照設定を行います。参照設定からMicrosoft ActiveX Data Objects 2.X Libraryにチェックを入れましょう。

f:id:drumer2sh:20140908125711p:plain

んで、コード。

Option Explicit

Private Sub ProductSearchButton_Click()
    Dim connectionString As String          '接続文字列
    Dim dbFilePass As String                'データベースファイルのパス
    
    Dim mCon As ADODB.Connection             'データベース接続オブジェクト
    Dim mRes As ADODB.Recordset              'レコードセットオブジェクト
    Dim strCon As String
    Dim Sql As String
    
    Dim i As Long, j As Long
    
    '接続文字列作成
    connectionString = "Microsoft.ACE.OLEDB.12.0"
    dbFilePass = "C:\SampleDb.accdb"
    
    
    'オブジェクト作成
    Set mCon = New ADODB.Connection
    
    ' 接続文字列を作成する
    strCon = "Provider=" & connectionString & ";" & "Data Source=" & dbFilePass & ";"
    
    '接続する
    mCon.Open strCon
    
    '''''''''''''''検索する'''''''''''''''
    'レコードセットオブジェクト作成
    Set mRes = New ADODB.Recordset
    
    ' 検索するSQL
    Sql = "SELECT * FROM Product"

    ' 検索する（検索するとレコードセットオブジェクトに検索結果が格納される。
    mRes.Open Sql, mCon, adOpenKeyset, adLockReadOnly
    
    i = 9
    Do Until mRes.EOF
    
        '出力する
        Sheet2.Cells(i, "G").Value = mRes.Fields("id").Value
        Sheet2.Cells(i, "H").Value = mRes.Fields("product_name").Value
        Sheet2.Cells(i, "I").Value = mRes.Fields("price").Value

        '次のレコードへ移る
        mRes.MoveNext

        '出力箇所カウンタ+1
        i = i + 1
    Loop
    
End Sub

ボタンをクリックするとこんな風に商品の一覧が表示されます。作ったのがSheet2の中なので、Sheet2に表示しています。

f:id:drumer2sh:20140908125730p:plain

ボタンを押すと・・・

f:id:drumer2sh:20140908125739p:plain

接続オブジェクトとレコードセットオブジェクト

AccessとExcelをつなぐためにはこの接続オブジェクトとレコードセットオブジェクトが必須です。接続オブジェクトを使って接続を行い、接続したオブジェクトを使って今度は検索を行い結果をレコードセットオブジェクトに格納します。接続オブジェクトを使って接続を行う時に利用するのが、Accessデータベースファイルがある場所・・・つまりパスです。それと接続文字列です。この2つを使ってAccessへ接続します。

データを検索する魔法のことばSQL

ここで、もう一つSQLというのがコメントにありますが、これがデータベースつまりAccessからデータを取り出すために必要な言葉です。この言葉をSQLと言います。SQLは非常に簡単です。見ればわかりますが、*は全部の列をという意味になります。直訳すれば、「Productから全部選んでね」ということです。ですので、Productテーブルがすべて選択されるわけです。この辺については別の記事でやります。もう少し詳しく。

後はこのSQLを使ってmRes.Openメソッドを使うと、レコードセットオブジェクトにデータが格納されます。