俺の開発研究所

Ruby on Rails、Linux、Windowsを中心にIT技術系の備忘録とか。よく忘れるので。

*

VBAによるSQL Serverを使用したデータベースアプリケーション開発

      2016/09/02

VBAで簡単なシステムを構築する場合は、データはデータベースなどの外部に保存する方が良い場合がある。

というかExcel上に保存するより、外部のデータベースに保存して、SQLでデータを操作する方がプログラムも組みやすい。

そこで、今回はVBAでデータベースアプリケーション開発ということで、SQL Serverへデータベース接続する方法、データ取得・追加・更新・削除の方法を調べてみた。

なお、今回のバージョンは以下です。

  • Microsoft Excel 2010
  • SQL Server 2008

スポンサード リンク

参照設定の追加

まず、VBAでデータベース接続を行う場合の設定を行う。

データベース接続を行う場合は、「ADODB」クラスを使用する。

そのためには、参照設定で「Microsoft ActiveX Data Objects 2.8 Library」というものを追加してやる必要がある。

Excelメニューの「開発」⇒「Visual Basic」で「Microsoft Visual Basic for Applications」(VBAのエディタ)を開く。

「ツール」⇒「参照設定」を開く。

そうすると、以下のライブラリが初期状態で選択されていると思う。

  • Visual Basic For Applications
  • Microsoft Excel 14.0 Object Library
  • OLE Automation
  • Microsoft Office 14.0 Object Library

そこに、以下を追加選択する。

  • Microsoft ActiveX Data Objects 2.8 Library

また、フォームを使用する場合は、以下を追加選択する。

  • Microsoft Forms 2.0 Object Library

データベースへの接続

続いて、データベース接続を行う。

データベース接続は、「ADODB」クラスの「Connection」オブジェクトを使用する。

「Connection」オブジェクトを生成し、「Open」メソッドでデータベース接続を行い、使い終われば、「Close」メソッドでデータベース接続を閉じるといった流れになる。

「ThisWorkbook」に、データベースオープン、データベースクローズ用の関数を用意しておき、それぞれワークブックオープン、クローズイベント時に呼び出し、コネクションをパブリック変数で保持しておく方法がシンプルで良いかなと思います。

そのブックを開いている間は、データベース接続状態になっているという感じです。

実際のソースは、以下のようになります。

なお、名前付きインスタンスの場合は、「Data Source」の指定が、【データベースサーバのIPアドレス(ホスト名)\インスタンス名】となるようだ。

データの取得(SELECT文の実行)

データベース接続ができたら、次はデータを取得する。

データの取得は、「ADODB」クラスの「Recordset」オブジェクトを使用する。

流れは以下のようになる。

「Recordset」オブジェクトを生成し、「Open」メソッドでSQL文を実行し、レコードセットを開く。

取得したレコードセットをループさせ、処理を行う。

使い終われば、「Close」メソッドでレコードセットを閉じる。

実際のソースは、以下のようになります。

注意点としては、ループの最後で「MoveNext」メソッドで、次のレコードへ移動するようにしておかないと、無限ループになってしまうところ。

ちなみに、レコード移動の関数で使用するのは以下のよう。

  • 「MoveFirst」…最初のレコードへ移動
  • 「MoveLast」…最後のレコードへ移動
  • 「MoveNext」…次のレコードへ移動
  • 「MovePrevious」…前のレコードへ移動

あと、「Application.ScreenUpdating = False」の1文で、画面描画を停止しているが、これをやっておかないとデータ件数が多い場合、表示がものすごく遅くなる可能性があるので注意。

データの追加・更新・削除(INSERT・UPDATE・DELETE文の実行)

最後に、データを追加・更新・削除する。

データの追加・更新・削除は、「ADODB」クラスの「Connection」オブジェクトを使用する。

上記のすでに生成した「Connection」オブジェクトを使用し、「Execute」メソッドでSQL文を実行し、データを追加・更新・削除する。

実際のソースは、以下のようになります。

以上。

 - SQL Server, VBA