■ データオブジェクトを使用してデータを取得しよう
ここではActiveXデータオブジェクト(ADO)とMicrosoftJetOLE DB4.0プロバイダを使用した、ワークシートからのデータ取得について解説します。
初級編の最後に、オブジェクト指向プログラミングによるデータセット取得のサンプルを記述しました。
しかし現実に、あのようなコードが使われることはまずないでしょう。
なぜならデータオブジェクトというデータベースのテーブルを直接操作できる便利なオブジェクトがすでに用意されており、ExcelVBAからでも簡単に使用することができるからです。
ADOにはレコードの追加・削除・検索・抽出・並び替えなどの基本的な操作が用意されており、SQL文を組み合わせることで複数のテーブルから、必要なデータセットを如何ようにでも抽出できます。
ですので開発者が自らレコードセットを扱うクラスを設計する必要はないのです。
では早速、このADOを使用してエクセルのワークシートをデータベースのテーブルのように使用してみましょう。
入門編9のコードを以下のように置き換えてみましょう。
実行するにはVBEの参照設定（ツール＞参照設定）で「Microsoft ActiveX Data ObjectX.X Library」にチェックを入れる必要があります。
（X.Xはバージョン名でできるだけ最新のものにチェックを入れてください）
' フォームモジュールに記述ここから −−−−−−−−−−−−−−−−−−−−−−−−−
Dim CN As ADODB.Connection
Private Sub UserForm_Initialize()
On Error GoTo ErrGyo
Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Excel 8.0"
CN.Open ThisWorkbook.FullName
Call ClearLabel
BtnOK.Caption = "OK"
Exit Sub
ErrGyo:
MsgBox "SyainMSTへの接続に失敗しました", vbCritical
BtnOK.Enabled = False
End Sub
Private Sub BtnOK_Click()
Dim RS As ADODB.Recordset
Dim SQL As String
Call ClearLabel
If ChkSyainID(TxtID) Then
SQL = "SELECT * FROM [SyainMST$] WHERE SyainID = " & TxtID
Set RS = New ADODB.Recordset
RS.Open SQL, CN, adOpenStatic, adLockReadOnly
If Not RS.BOF Then
LblName = RS.Fields("SyainNAME")
LblKinzoku = RS.Fields("Kinzoku")
LblSyozoku = RS.Fields("Syozoku")
LblYakusyoku = RS.Fields("Yakusyoku")
Else
MsgBox "入力された社員IDは存在しません", vbCritical
End If
Set RS = Nothing
Else
MsgBox "社員IDが有効ではありません", vbCritical
End If
End Sub
Private Function ChkSyainID(ByVal pTxtID As String) As Boolean
Dim i As Long
If IsNumeric(pTxtID) Then
If Len(pTxtID) = 4 Then
ChkSyainID = True
Exit Function
End If
End If
ChkSyainID = False
End Function
Private Sub UserForm_Terminate()
Set CN = Nothing
End Sub
' フォームモジュールに記述ここまで −−−−−−−−−−−−−−−−−−−−−−−−−
標準モジュールのTestプロシージャとフォームモジュールのClearLabel関数は前回のものをそのまま使用します。
クラスモジュールSyainClsは今回使用しません。
ずいぶんと見慣れないコードが並んでいますね。順を追って説明していきます。
まずUserForm_InitializeのときにADOのコネクションを確立します。
Dim CN As ADODB.Connection はモジュールレベルでコネクション変数を定義しています。以降、UserForm_Terminate されるまでこのコネクション変数はモジュール内のどこからでも呼び出しが可能です。
Set CN = New ADODB.Connection でコネクション変数のインスタンスを作成しました。
CN.Provider = "Microsoft.Jet.OLEDB.4.0" は使用するOLE DBプロバイダを指定しています。Accessに接続する時も同じものを使用します。
CN.Properties("Extended Properties") = "Excel 8.0" 拡張プロパティにExcel8.0を指定することで97以降のExcelに接続できるようになります。
CN.Open ThisWorkbook.FullName 現在開いているブックのフルパス+ファイル名を指定します。つまり自分の中にあるシートにアクセスさせるわけです。
さて、フォームが開いた段階でADOのコネクションが確立しています。しかしレコードセットはまだなにも取得していません。BtnOK_Click が押された時に初めてレコードセットを取得するのです。
Dim RS As ADODB.Recordset レコードセット変数を定義しています。
SQL = "SELECT * FROM [SyainMST$] WHERE SyainID = " & TxtID これがSQL文と呼ばれるものです。このクエリー文次第で様々なレコードセットを取得することが可能です。
FROM [SyainMST$] でレコードセットに取得するワークシート名を指定しています。
WHERE SyainID = " & TxtID はTxtIDの値に一致したレコードを取得しなさいという意味になります。
Set RS = New ADODB.Recordset レコードセットのインスタンスを実体化しました。
RS.Open SQL, CN, adOpenStatic, adLockReadOnly レコードセットRSにOpenメソッドをおくりレコードセットを取得しています。adOpenStatic はカーソルタイプ adLockReadOnly はロックタイプです。今回は参照のみに使用するのでこの値をセットしています。カーソル・ロックの種類には以下のようなものがあります。
| カーソルの種類 |
定数 |
値 |
説明 |
| 順方向専用 |
adOpenForwardOnly |
0 |
レコードセットを順方向のみ移動します。個別のレコードを検索するためには使用できません。レコードセット内のレコードの数を返すことはできません |
| キーセット |
adOpenKeyset |
1 |
レコードセットを順方向および逆方向にスクロールします。Find の使用が可能で、レコード数を返します。既存のレコードの変更は動的に反映され、新しいレコードの作成は反映されません |
| 動的 |
adOpenDynamic |
2 |
レコードセットを順方向および逆方向にスクロールします。Find の使用が可能で、レコード数を返します。レコードセットは全ての変更が反映されます |
| 静的 |
adOpenStatic |
3 |
レコードセットを順方向および逆方向にスクロールします。Find の使用が可能で、レコード数を返します。レコードセットは変更が動的に反映されません |
| ロックの種類 |
定数 |
値 |
説明 |
| 読み取り専用 |
adLockReadOnly |
1 |
レコードをロックせずリソースを解放します。ただしレコードセットは読み取り専用になります |
| ペシミスティック |
adLockPessimistic |
2 |
編集開始の時点でレコードをロックしたら、Update メソッドを呼び出すまで解放しません |
| オプティミスティック |
adLockOptimistic |
3 |
Update メソッドを呼び出すときのみ、レコードを瞬間的にロックします |
| 一括 |
adLockBatchOptimistic |
4 |
一括更新に使用します |
レコードセットを読み取りだけでなく更新する必要があるならば adOpenDynamic, adLockOptimistic の値をセットしてやればいいわけです。
If Not RS.BOF Then 「BOF」プロパティは最初のレコードの前方にカーソルがあるということです。「EOF」は逆に最後のレコードの後方にカーソルがある状態を表します。BOFがTRUEのときはレコードが一件もないということをさしています。
LblName = RS.Fields("SyainNAME") フィールドプロパティの名前は取り込んだワークシートの一行目が項目名になります。SyainNAME は社員名でしたね。以降、同じ要領でラベルに項目値をセットしていきます。
Set RS = Nothing 必要のなくなったRSオブジェクトを廃棄しています。
ADOを使用することでこんなに簡単にワークシートをデータベースとして使用することができました。
このやり方の素晴らしい点は、将来データベースをワークシートからMDBファイル、SQLサーバーに移行したときもほとんど同じコードが再利用できる点です。
また現時点でSQLやOracleサーバーを使用している場合も、エクセルのワークシートをワークファイル代りに使用することができます。
非常に応用のきく、汎用性のある手法です。
次回は同じ方法を使って、ExcelからAccessのデータベースを操作したいと思います。
※ 今回の内容に関する詳しいMicrosoftのリファレンスはこちらです。
http://support.microsoft.com/kb/257819
http://support.microsoft.com/default.aspx?scid=kb;ja;278973
|
