![](https://www.shiromaruchan.com/wp5/wp-content/themes/cocoon-master/images/obasan.png)
はぁ~。なんだかデータベースに接続したい気分だわー
![](https://www.shiromaruchan.com/wp5/wp-content/themes/cocoon-master/images/man.png)
そんな時はシロマル先生に聞いてみよー。
![](https://www.shiromaruchan.com/wp5/wp-content/themes/cocoon-master/images/woman.png)
・・・
色々な方法でExcelから、AccessやSQL Serverなどのデータベースにアクセスして、データを取得することが可能です。
業務用アプリは一般的にデータベースに対してデータを保存していますので、データベースへの接続さえできれば、アプリ内の情報をごっそり取り出して、自由に加工が出来ます。
データベースへ接続するために必要な情報は、一般的には、IPアドレスまたはサーバー名(アクセスの場合はファイル保存先)・ユーザー名・パスワード・接続先データベース名・テーブル名などです。
基本的には開発元かシステム管理者へ確認すれば、それらの情報は教えてもらえるはずですが、そもそもデータベースへの接続を禁止しているような場合もあり得ますので、まずは開発元へご確認ください。
また、アクセスする際は、なるべく読み取り専用のユーザーにしましょう。誤ってデータの書き換えを行ってしまうと最悪システムが動かなくなる場合もあり得ますので、ご注意ください。
データベースアクセス方法は色々ありますが、今回は下記の3種類の方法をお伝えします。
種類 | 特徴 |
Power Query | プログラム不要。 |
VBA No SQL | SQLの知識不要。VBAさえ分かっていれば分かりやすい。 複雑な条件の抽出には不向き。 |
VBA SQL | データベース操作は何でも可能。SQL文の書き方を 覚える必要あり。 |
それでは実際の使用方法を見てみましょう。今回はAccessに接続するサンプルになります。
まずは下記のような情報が入ったAccessがあるとします。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/01/image-4.png)
Power Query
データタブのデータの取得 – データベースから – Microsoft Accessデータベースからをクリック。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-5.png)
該当のAccessのファイルを指定すると、下記の画面になります。
対象のテーブルを選択して、読み込みを押せば、そのまますべてのデータが反映されます。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-6.png)
データを更新する場合は、下記の更新をクリック、または右クリックで更新を押すだけです。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-7.png)
※SQL Serverの場合
データタブのデータの取得 – データベースから – SQL Serverデータベースからをクリック。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-9.png)
サーバー名、データベース名、SQL文(必要に応じて)などを入力してOK。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-1.png)
SQL Server認証の場合は、左のデータベースを選択して、ユーザー名、パスワードを入力して、接続をクリック。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-2.png)
下記が出たらOKをクリック。
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-8.png)
SQL文を指定していない場合は、データベースとテーブルを選択すれば、同様にデータが取れます。
VBA No SQL
参照設定でMicrosoft DAO x.x Object Libraryを追加します。(バージョンは使用しているExcelのバージョンによって異なります。)
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-3.png)
コードもとてもシンプルです。
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メソッドなどを使用しますが、テーブルの結合などにはあまり向いていないようです。。(たぶん)
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/cocoon-resources/blog-card-cache/62becbf9e66edbd7a674a2191e28faef.png)
1つのテーブルに対して全件、または簡単な条件でデータを取るときに向いています。
VBA SQL
VBAを使用してデータを取る場合、この方法が一般的です。ネットでもたくさん記事が出てきます。
参照設定でMicrosoft ActiveX Data Objects 2.x Libraryを追加します。(6.1ではありません)
![](https://www.shiromaruchan.com/wp5/wp-content/uploads/2022/04/image-4.png)
さっきの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文が複雑になりすぎてメンテナンス性が低くなる場合がよくありますので、なるべくシンプルにするのをお勧めします。