Excelからデータベースへ接続する色々な方法

色々な方法でExcelから、AccessやSQL Serverなどのデータベースにアクセスして、データを取得することが可能です。

業務用アプリは一般的にデータベースに対してデータを保存していますので、データベースへの接続さえできれば、アプリ内の情報をごっそり取り出して、自由に加工が出来ます。

データベースへ接続するために必要な情報は、一般的には、IPアドレスまたはサーバー名（アクセスの場合はファイル保存先）・ユーザー名・パスワード・接続先データベース名・テーブル名などです。

基本的には開発元かシステム管理者へ確認すれば、それらの情報は教えてもらえるはずですが、そもそもデータベースへの接続を禁止しているような場合もあり得ますので、まずは開発元へご確認ください。

また、アクセスする際は、なるべく読み取り専用のユーザーにしましょう。誤ってデータの書き換えを行ってしまうと最悪システムが動かなくなる場合もあり得ますので、ご注意ください。

データベースアクセス方法は色々ありますが、今回は下記の3種類の方法をお伝えします。

種類	特徴
Power Query	プログラム不要。
VBA No SQL	SQLの知識不要。VBAさえ分かっていれば分かりやすい。 複雑な条件の抽出には不向き。
VBA SQL	データベース操作は何でも可能。SQL文の書き方を 覚える必要あり。

それでは実際の使用方法を見てみましょう。今回はAccessに接続するサンプルになります。

まずは下記のような情報が入ったAccessがあるとします。

Power Query

データタブのデータの取得 – データベースから – Microsoft Accessデータベースからをクリック。

該当のAccessのファイルを指定すると、下記の画面になります。

対象のテーブルを選択して、読み込みを押せば、そのまますべてのデータが反映されます。

データを更新する場合は、下記の更新をクリック、または右クリックで更新を押すだけです。

※SQL Serverの場合

データタブのデータの取得 – データベースから – SQL Serverデータベースからをクリック。

サーバー名、データベース名、SQL文(必要に応じて)などを入力してOK。

SQL Server認証の場合は、左のデータベースを選択して、ユーザー名、パスワードを入力して、接続をクリック。

下記が出たらOKをクリック。

SQL文を指定していない場合は、データベースとテーブルを選択すれば、同様にデータが取れます。

VBA No SQL

参照設定でMicrosoft DAO x.x Object Libraryを追加します。（バージョンは使用しているExcelのバージョンによって異なります。）

コードもとてもシンプルです。

Sub dataGet()
    
    Dim dbe As DAO.DBEngine
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rowCount As Integer
    
    Set dbe = CreateObject("DAO.DBEngine.120")
    
    Set dbs = dbe.OpenDatabase("C:\Users\user\Desktop\Customers.accdb")
    Set rst = dbs.OpenRecordset("Customers")

    rowCount = 2

    Do Until rst.EOF
    
        Cells(rowCount, 1) = rst!CustomerID
        Cells(rowCount, 2) = rst!顧客名
        Cells(rowCount, 3) = rst!住所
        
        rowCount = rowCount + 1
        rst.MoveNext
    
    Loop
   
Cleanup:
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Set dbe = Nothing
    
End Sub
  

抽出条件を絞る場合は下記のようなFindNextメソッドなどを使用しますが、テーブルの結合などにはあまり向いていないようです。。（たぶん）

Recordset.FindNext メソッド (DAO)

Office 開発者クライアント VBA リファレンス

learn.microsoft.com

1つのテーブルに対して全件、または簡単な条件でデータを取るときに向いています。

VBA SQL

VBAを使用してデータを取る場合、この方法が一般的です。ネットでもたくさん記事が出てきます。

参照設定でMicrosoft ActiveX Data Objects 2.x Libraryを追加します。（6.1ではありません）

さっきのNo SQLのパターンと似ていますが、SQL文を使用しています。

Sub dataGet()

    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    Dim strSQL As String
    Dim rowCount As Integer
 
    'データベースに接続する
    conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\user\Desktop\Customers.accdb"
    conn.Open
 
    'データ取得用SQL
    strSQL = "SELECT * FROM Customers"
 
    'レコードセットオープン
    rst.Open strSQL, conn, adOpenDynamic
 
    rowCount = 2
 
    Do Until rst.EOF
    
        Cells(rowCount, 1).Value = rst!CustomerID
        Cells(rowCount, 2).Value = rst!顧客名
        Cells(rowCount, 3).Value = rst!住所
        
        rowCount = rowCount + 1
        rst.MoveNext
    
    Loop
 
Cleanup:
    rst.Close
    Set rst = Nothing
    Set conn = Nothing
 
End Sub

SQL文はテーブル結合などの複雑な条件でもデータが簡単に取れやすい反面、SQL文が複雑になりすぎてメンテナンス性が低くなる場合がよくありますので、なるべくシンプルにするのをお勧めします。