興味があったので
特にこれがなきゃダメとかいう事情は無かったんですけど、ExcelのVBA（マクロ）でデータベースに接続する（select * …みたいなSQLを発行する）ってのをやってみたかったんですよね。
しかも、あるPCでデータベースを動かして、別のPCのExcelからつなぐみたいなことをしてみたかった。
厳密にはVBAじゃなくても何でも良かったんですけど、プログラムの開発環境とか準備していないし知識も乏しいので、今自分のPCに入っているExcelでできるならそれがお手軽だったということでVBAです。
で、やろうとしたら何が問題かというとどこから手を付けていいのかがわからなかったので、最終的にできたところから振り返ってみて、本当に概要だけ、全体の流れだけメモしておきたいと思いました。
細かいところはバージョンによって変わったりすると思うので、都度ググって先駆者の情報を参照するのがいいと思います。
やったこと概要
ザックリこういうことをやりましたメモ。
あくまでも個人的に勝手にやってみたことなので何か間違っているところもあると思います。
- データベースとしてpostgreSQLをインストール。
本当はOracle Express Edition（無料のOracle）で行こうとやってみたけど、VBAからうまく接続できず諦めてpostgreSQLに変更。
postgreSQLインストーラ最後のオプション（関連プログラムのインストール）はチェックをオフにして終了。簡単に操作できるpgAdmin4というツールも入ってきた。 - （必須ではないけど）postgreをいつも使うわけではないので、PCが重くならないようにpostgreのサービスを自動起動ではなく手動起動に変更してみた。
DBを使用するときに簡単に起動できるように net startコマンドで起動するバッチファイルを用意。実行には要管理者権限。 - 他のPC（以下、クライアント）からDB（サーバ）に接続できるか試したかったので、クライアントにpgAdmin4を入れてみたがなんか文字コードか何かのエラーが出てつながらないのでpgAdminIIIをインストールした。
IIIの方がソフト自体、日本語化されているのでいいかもしれない。サーバにもIIIを入れておいた。 - まだクライアントのpgAdminIIIからサーバのpostgreSQLにつながらないので、サーバのWindowsファイアウォールでTCP5432を許可。→クライアントからつながるようになった。
- クライアントで、Excelから接続するために、postgreのODBCドライバをインストール。Excelが32ビットだったので、32ビット（x86）をインストール。
続いてコントロールパネルではODBC データベース（32ビット）の方を使って設定。設定画面では特に設定せず即保存。ユーザデータソースとして「PostgreSQL35W」が登録された。 - ExcelのVBAの画面で、ツール＞参照設定より”Microsoft ActiveX Data Objects 6.1 Library”を追加。たぶんバージョンは新しいの選べば良さそう。
- VBAでだいたい次のようなコードで接続する。この後にSQL発行したり接続をクローズしたりする。
Dim Con As ADODB.Connection
Set Con = New ADODB.ConnectionCon.Open “Provider=MSDASQL; DSN=PostgreSQL35W;DATABASE=（dbの名前）;SERVER=（サーバのマシン名 or IP）;PORT=5432;UID=postgres;PWD=（パスワード）;SSLmode=disable”
まとめ
……というような感じで、家の同じWiFiにつないでいる2台のPCで、データベースサーバとクライアントみたいなことができました。
何かと迷ったし、ここまでやったからなんだってのはありますけど、うまくいったので嬉しかったです。
おわり。
カンタン！ だけど深い！ Excel VBAで行うデータベース活用の教科書2007/2010/2013対応
- 作者: 早坂清志
- 出版社/メーカー: 秀和システム
- 発売日: 2015/06/29
- メディア: Kindle版
コメント