技術情報
[02] データベース
ExcelVBAでActiveX Data Objects(ADO)を使って、データベースを操作する方法について説明します。

[ 1] 設定


Visual Basic Editorのメニューから「ツール」→「参照設定」を選択します。


参照設定画面が表示されますので、
「Microsoft ActiveX Data Object *.* Library」を選択します。
*.*にはバージョンの番号が入っていますので、参照したいバージョンのものを選択してください。
様々なクライアント環境で使用することを想定している場合には、使用できるバージョン番号に注意しなければなりません。
Excelの古いバージョンでも使えるようにするには、ADOのバージョンも小さい番号のものを使う必要がありますが、基本はバージョンの新しいものを使います。

[ 2] データベースへの接続準備

Visual Basic Editorを開いて、以下の接続用コードを記述します。
Publicなサブルーチン化して共通利用できるようにするのも良いですし、イベントごとに以下のコードをその都度記述しても構いません。

(MS-Accessへの接続例)
' データベースに接続するための変数を宣言します
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rst As ADODB.Recordset

' 宣言した変数にデータベース接続のためのインスタンスをセットします
Set Cnn = New ADODB.Connection  ' 接続用オブジェクト
Set Cmd = New ADODB.Command     ' SQLコマンド用オブジェクト
Set Rst = New ADODB.Recordset   ' レコードセット用オブジェクト

' 以下の例はMS-Accessへの接続例です
' データベースファイルのパスを「C:\testdb.mdb」とします
' また、データベースにはTestTableが定義されているとします
' (他にオプションがあれば、必要に応じて追加してください)
Cnn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\testdb.mdb;Password=test"
' 拡張子.accdbのMS-Accessへの接続は、
' Cnn.ConnectionString = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\testdb.accdb"

' データベースに接続し、
' SQLコマンド用オブジェクトに接続します
Cnn.Open
Set Cmd.ActiveConnection = Cnn

~ SQL文によるデータベース操作 ~

' データベースを閉じる
Cnn.Close

(PostgreSQLへの接続例)
' データベースに接続するための変数を宣言します
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rst As ADODB.Recordset

' 宣言した変数にデータベース接続のためのインスタンスをセットします
Set Cnn = New ADODB.Connection  ' 接続用オブジェクト
Set Cmd = New ADODB.Command     ' SQLコマンド用オブジェクト
Set Rst = New ADODB.Recordset   ' レコードセット用オブジェクト

' 以下の例はPostgreSQLへの接続例です
'   サーバ名:dbserver
'   データベース名:testdb
'   ユーザ名:testuser
'   パスワード:testpw
' (他にオプションがあれば、必要に応じて追加してください)
' とします
Cnn.ConnectionString = "Driver={PostgreSQL};Server=dbserver; Database=testdb; Uid=testuser; PWD=testpw"

' データベースに接続し、
' SQLコマンド用オブジェクトに接続します
Cnn.Open
Set Cmd.ActiveConnection = Cnn

~ SQL文によるデータベース操作 ~

' データベースを閉じる
Cnn.Close

(MySQLへの接続例)
' データベースに接続するための変数を宣言します
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rst As ADODB.Recordset

' 宣言した変数にデータベース接続のためのインスタンスをセットします
Set Cnn = New ADODB.Connection  ' 接続用オブジェクト
Set Cmd = New ADODB.Command     ' SQLコマンド用オブジェクト
Set Rst = New ADODB.Recordset   ' レコードセット用オブジェクト

' 以下の例はMySQLへの接続例です
'   サーバ名:dbserver
'   データベース名:testdb
'   ユーザ名:testuser
'   パスワード:testpw
' とします
' (他にオプションがあれば、必要に応じて追加してください)
' また、ドライババージョンは3.51とします
Cnn.ConnectionString = "Driver={mySQL ODBC 3.51 Driver};Server=dbserver; Database=testdb; Uid=testuser; PWD=testpw"

' データベースに接続し、
' SQLコマンド用オブジェクトに接続します
Cnn.Open
Set Cmd.ActiveConnection = Cnn

~ SQL文によるデータベース操作 ~

' データベースを閉じる
Cnn.Close

(SQL Serverへの接続例)
' データベースに接続するための変数を宣言します
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rst As ADODB.Recordset

' 宣言した変数にデータベース接続のためのインスタンスをセットします
Set Cnn = New ADODB.Connection  ' 接続用オブジェクト
Set Cmd = New ADODB.Command     ' SQLコマンド用オブジェクト
Set Rst = New ADODB.Recordset   ' レコードセット用オブジェクト

' 以下の例はMySQLへの接続例です
'   サーバ名:dbserver
'   データベース名:testdb
'   ユーザ名:testuser
'   パスワード:testpw
' とします
' (他にオプションがあれば、必要に応じて追加してください)
Cnn.ConnectionString = "Driver={SQL Server};Server=dbserver; Database=testdb; Uid=testuser; PWD=testpw"

' データベースに接続し、
' SQLコマンド用オブジェクトに接続します
Cnn.Open
Set Cmd.ActiveConnection = Cnn

~ SQL文によるデータベース操作 ~

' データベースを閉じる
Cnn.Close

[ 3] データベースの操作方法

データベースへの接続が完了したら、後はSQLコマンドにより実行した結果をレコードセット用オブジェクトで受け取って操作をしていきます。

' SQL文によりデータベースを操作します
' 実行結果はレコードセット用オブジェクトに取得します
Cmd.CommandText = "SELECT * FROM TestTable"
Set Rst = Cmd.Execute
' レコードセットの最後まで繰り返し処理します
Do While Not Rst.Eof

  ~ データ操作 ~

  Rst.MoveNext
Loop