エクセルExcel大事典 VBAマクロ応用講座 ADO 接続 SQLサーバServer ODBC

GEN MUTO'S HOMEPAGE　＞　エクセル大事典　＞　エクセルVBAを極める

　■ エクセルVBAを極める－VBAスーパーテクニック

Microsoft SQL Serverに接続してみよう！

　
■ SQL-Serverへ接続してみよう！
　
データベースへの接続編、今回はエクセルからMicrosoftSQL Server2005へ接続してみましょう。
筆者の環境がSQL2005ですので、SQL2005を元に説明しますが、SQL2000、SQL7.0でもVBAのコードは基本的に同じになります。

まずSQLサーバにtestという名前のデータベースを用意してください。領域は10MBも確保すれば十分でしょう。この中にSyainMSTを定義します。
Management StudioやEnterprise Manager等で直接テーブルを作成してもいいですが、Accessのアップサイジングウィザードを使用すると楽ちんです。前回のtest.mdbがそのまま使えます。ついでにデータも送っておきましょう。
（ウィザードを出すには、ツール＞データベースユーティリティ＞アップサイジングウィザード）

さて接続する方法はいくつかありますが、今回はその代表的な方法を3種類紹介します。

１．コードの中から直接ＩＰアドレスを指定する方法
前回のAccessへの接続はこのやり方でしたね。

　　Set CN = New ADODB.Connection
　　　　CN.Provider = "Microsoft.Jet.OLEDB.4.0"
　　　　CN.ConnectionString = ThisWorkbook.Path & "\test.mdb"
　　　　CN.Open

これをこのように変更します。

　　Set CN = New ADODB.Connection
　　　　CN.Provider = "SQLOLEDB"
　　　　CN.ConnectionString = "Data Source=192.168.X.X;" & _
　　　　　　　　　　　　　　　　　　　"Initial Catalog=test;" & _
　　　　　　　　　　　　　　　　　　　"User ID=sa;" & _
　　　　　　　　　　　　　　　　　　　"Password="
　　　　CN.Open

CN.Provider の指定が、"Microsoft.Jet.OLEDB.4.0"から"SQLOLEDB"に変わっています。今回接続する先はSQL Serverなので当然、OLEDBプロバイダはSQLOLEDBを指定します。

CN.ConnectionString がずいぶんごちゃごちゃしましたね。
Data Source=192.168.X.X の部分で接続先サーバのIPアドレスを指定します。
Initial Catalog=testは接続するデータベース名です。今回はtestという名前のデータベースをSQLサーバに用意しましたね。
User ID=saとPassword=の部分はSQLサーバのログインアカウントです。お使いのSQLサーバのログインプロパティにあわせてください。（今回はいわゆる"saのパスなし"というやつで説明します）

さて前回のコードを書き変えたら早速接続してみてください。データは表示されましたか？

この方法はコードの中に接続情報をすべて記述しているので他になにも用意しなくてもすぐに接続ができる点が良い点です。しかし逆にいうとサーバーのIPアドレスが変わったりデータベース名が変更になればコードを修正する必要がでてきます。

２．ＯＤＢＣドライバ（システムDSN）による接続

次はODBCドライバによる接続に挑戦してみましょう。
先ほどのように接続文字列をコードの中に記述すると接続環境が変わったときにコードを修正する必要がありました。これは出来る限り避けたいことです。
そこで接続に係わる情報は専用のドライバソフトに一元管理させよう、プログラムからはそのデータソースを呼び出すだけで簡単にデータベースに接続できるようにしよう。という発想のもとMicrosoftより提唱されたデータベースアクセスの標準仕様がODBC（Open DataBase Connectivity）です。

まずODBCドライバにシステムDSNを追加してみましょう。
スタート＞プログラム＞管理ツール＞データソース（ODBC）を選択します。ODBCデータソースアドミニストレータが起動します。システムDSNタブを選択、追加ボタンをクリックします。
データソースドライバの選択はSQL Server（一番下にあります）、データソースの名前はプログラムから呼び出すDSNの部分です。今回は"SysDSNtest"にしましょう。接続するサーバーにサーバのIPアドレス"192.168.X.X"を、次の画面ではログインIDとパスワードを設定します。さらに次の画面で既定のデータベースに"test"を選択。完了時にでるデータソースのテストで接続を確認してください。
システムデータソースにSysDSNtestが追加されました。

さあこのデータソースを早速プログラムから呼び出してみましょう。

　　Set CN = New ADODB.Connection
　　　　CN.ConnectionString = "Data Source=SysDSNtest;" & _
　　　　　　　　　　　　　　　　　　"User ID=sa;" & _
　　　　　　　　　　　　　　　　　　"Password="
　　　　CN.Open

接続文字列がさっぱりしましたね。
Data Source=SysDSNtest の部分で先ほど作ったシステムDSNを呼び出す指定をしています。SysDSNtestのなかに接続環境のプロパティが記録されていますので、その情報をもとにSQLサーバに接続できるというわけです。

この方法のよいところは将来接続環境が変わったときにSysDSNtestの構成を変更するだけで、コードの修正が不要である点です。

３．ＯＤＢＣドライバ（ファイルDSN）による接続

さてシステムDSNは確かに便利ですが、大規模オフィスのシステム変更があった場合、端末数が100台を超えるケースなどザラにあります。100台を超えるPCのシステムDSNを一台ずつ修正していくのはすごく骨が折れる作業ですし、あまり現実的だとはいえません。
そこでファイルDSNを使ってみましょう。これは接続情報の内容をそのまま拡張子dsnのファイルに書き出してくれます。
作り方は先ほどのソースアドミニストレータからファイルDSNタブを選択、最初にファイルを保存する場所を指定してやり、後はシステムDSNと同じやりかたで作成できます。データソースの名前は"FileDSNtest"にしておきましょう。

プログラムからはこのファイルDSNのある場所を指定してやるだけでＯＫです。
このファイルDSNをファイルサーバの共有スペースなどに置き、プログラムのほうで共有スペースのパスを指定してやればいいのです。
修正が必要な時は、サーバ内のファイルDSNを修正してやればすべての端末に修正が反映されます。
"FileDSNtest.dsn"はテキストファイルなのでテキストエディタ等で簡単に修正が可能です。

　　Set CN = New ADODB.Connection
　　　　CN.ConnectionString = "FILEDSN=" ThisWorkbook.Path & _
　　　　　　　　　　　　　　　　　　"\FileDSNtest.dsn;"
　　CN.Open

サンプルでは実行しているブックと同じフォルダ内にFileDSNtest.dsnがあるものとしています。

"FileDSNtest.dsn"の中身は、
[ODBC]
DRIVER=SQL Server
UID=sa
DATABASE=test
APP=Microsoft Data Access Components
SERVER=192.168.X.X
こんな感じです。接続情報が記録されているだけですね。

今回いろいろなデータベースに接続する方法を紹介しましたが、どの方法も一長一短があります。

システムの規模、電算室等のメンテナンス要員の有無、将来のデータベースサーバ運用予定、等を考慮されたうえで最適な接続方法を決定されるのがよいかと思います。

　

＞＞次の話にすすむ

サイト内検索ができます

Microsoft Most Valuable Professional

Microsoft MVP Excel
武藤玄プロフィール
　

■■■ 執筆した書籍 ■■■
　

EXCEL VBA 業務自動化
　

 Excel VBA を５日でマスターする本
　

 仕事を速くする Excel VBA 入門
　

 Excel VBAと業務改善のポイントがわかる本
　

 Access VBA ベーシックスタンダード
　

 Excel VBA アクションゲーム作成入門
　

■■■ 連載中の記事 ■■■
　

Excel VBAで業務改善！
　

実務で使うExcel VBA
　

■ ＴＯＰページ

■ エクセルVBA超入門

　・９ステップで"Hello World!"
　　からオブジェクト指向まで！
　　1."Hello World!"を表示しよう
　　2.基本はデータの入出力
　　3.繰り返し処理
　　4.シートで簡易データベース
　　5.名前をつけよう
　　6.構造化プログラムに挑戦
　　7.構造体を使ってみよう
　　8.フォームを使ってみよう
　　9.オブジェクト指向に挑戦

エクセルExcel大事典ははリンクフリーです
お問い合わせはこちら gengengen@yahoo.co.jp