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」に、データベースオープン、データベースクローズ用の関数を用意しておき、それぞれワークブックオープン、クローズイベント時に呼び出し、コネクションをパブリック変数で保持しておく方法がシンプルで良いかなと思います。
そのブックを開いている間は、データベース接続状態になっているという感じです。
実際のソースは、以下のようになります。
'変数
Public db As ADODB.Connection
'ワークブックオープン
Private Sub Workbook_Open()
'DBオープン
Call ThisWorkbook.DbOpen
End Sub
'ワークブッククローズ前
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'DBクローズ
Call ThisWorkbook.DbClose
End Sub
'DBオープン
Public Sub DbOpen()
'変数
Dim openFl As Boolean
'オープンフラグオフ
openFl = False
If db Is Nothing Then
'オブジェクトが存在しない場合
'オブジェクト生成
Set db = New ADODB.Connection
'オープンフラグオン
openFl = True
Else
'オブジェクトが存在する場合
'コネクション状態がクローズの場合
If db.State = adStateClosed Then
'オープンフラグオン
openFl = True
End If
End If
'オープンフラグがオンの場合
If openFl = True Then
'DBオープン
db.Open "Provider=SQLOLEDB;" & _
"Data Source=【データベースサーバのIPアドレス（ホスト名）】;" & _
"Initial Catalog=【データベース名】;" & _
"User Id=【ユーザーID】;" & _
"Password=【パスワード】;"
End If
End Sub
'DBクローズ
Public Sub DbClose()
'オブジェクトが存在する場合
If Not db Is Nothing Then
'コネクション状態がオープンの場合
If db.State = adStateOpen Then
'DBクローズ
db.Close
End If
'オブジェクト破棄
Set db = Nothing
End If
End Sub
なお、名前付きインスタンスの場合は、「Data Source」の指定が、【データベースサーバのIPアドレス（ホスト名）\インスタンス名】となるようだ。
データの取得（SELECT文の実行）
データベース接続ができたら、次はデータを取得する。
データの取得は、「ADODB」クラスの「Recordset」オブジェクトを使用する。
流れは以下のようになる。
「Recordset」オブジェクトを生成し、「Open」メソッドでSQL文を実行し、レコードセットを開く。
取得したレコードセットをループさせ、処理を行う。
使い終われば、「Close」メソッドでレコードセットを閉じる。
実際のソースは、以下のようになります。
'変数
Dim rs As ADODB.Recordset
Dim strSQL As String
'テストデータの取得
Public Sub GetTestData()
'画面描画の停止
Application.ScreenUpdating = False
'オブジェクト生成
Set rs = New ADODB.Recordset
'テストデータ取得SQL
strSQL = "SELECT " & _
" COL1, " & _
" COL2 " & _
"FROM " & _
" TEST "
'レコードセットオープン
rs.Open strSQL, ThisWorkbook.db, adOpenKeyset, adLockReadOnly
'ループカウンタ
i = 0
'取得データループ
Do Until rs.EOF
'データをセルへ設定
Cells(i, 1) = rs!COL1
Cells(i, 2) = rs!COL2
'ループカウンタインクリメント
i = i + 1
'次のレコードへ
rs.MoveNext
Loop
'レコードセットクローズ
rs.Close
Set rs = Nothing
End Sub
注意点としては、ループの最後で「MoveNext」メソッドで、次のレコードへ移動するようにしておかないと、無限ループになってしまうところ。
ちなみに、レコード移動の関数で使用するのは以下のよう。
- 「MoveFirst」…最初のレコードへ移動
- 「MoveLast」…最後のレコードへ移動
- 「MoveNext」…次のレコードへ移動
- 「MovePrevious」…前のレコードへ移動
あと、「Application.ScreenUpdating = False」の1文で、画面描画を停止しているが、これをやっておかないとデータ件数が多い場合、表示がものすごく遅くなる可能性があるので注意。
データの追加・更新・削除（INSERT・UPDATE・DELETE文の実行）
最後に、データを追加・更新・削除する。
データの追加・更新・削除は、「ADODB」クラスの「Connection」オブジェクトを使用する。
上記のすでに生成した「Connection」オブジェクトを使用し、「Execute」メソッドでSQL文を実行し、データを追加・更新・削除する。
実際のソースは、以下のようになります。
'変数
Dim strSQL As String
'テストデータの追加
Public Sub InsTestData()
'画面描画の停止
Application.ScreenUpdating = False
'テストデータ追加SQL
strSQL = "INSERT INTO TEST VALUES ( " & _
" 1, " & _
" 2 " & _
") "
'SQL文実行
ThisWorkbook.db.Execute (strSQL)
End Sub
'テストデータの更新
Public Sub UpdTestData()
'画面描画の停止
Application.ScreenUpdating = False
'テストデータ更新SQL
strSQL = "UPDATE TEST SET " & _
" COL1 = 1, " & _
" COL2 = 2 " & _
"WHERE " & _
" COL1 = 3 "
'SQL文実行
ThisWorkbook.db.Execute (strSQL)
End Sub
'テストデータの削除
Public Sub DelTestData()
'画面描画の停止
Application.ScreenUpdating = False
'テストデータ削除SQL
strSQL = "DELETE FROM TEST " & _
"WHERE " & _
" COL1 = 3 "
'SQL文実行
ThisWorkbook.db.Execute (strSQL)
End Sub
以上。
コメント