ADO Recordset - Database - Excel 97 - vba - 表計算゛ (Hyo-Kei-Zan)

実例

ADO の最初のハードルは、データベースへの接続方法を記述する ConnectionString (接続文字列) でしょう。逆に、これさえ乗り越えれば、目先、データベースを扱うのに、大きな障害はなくなります。ADO では、様々なデータベースが利用できるように、接続文字列の記述方法に何通りもの書き方があります。その柔軟さが却って、記述方法の理解を妨げるため、とっつきにくい印象を与えます。

ここでは、接続文字列の説明を後に回し、まず、 Recordset Object の概要を説明します。

ADO Recordset を読む

次の関数 ado1 は、 ADO接続文字列と SQL文字列をもらって、最初のレコードの内容を返します。
```
Function ado1(Con As String, SQL As String)
    Const adOpenForwardOnly = 0
    Dim Rs1 As Object, FieA As Object
    Dim a As String
    a = ""
    Set Rs1 = CreateObject("ADODB.Recordset")
    Rs1.Open SQL, Con, adOpenForwardOnly
    If Not Rs1.EOF Then
        For Each FieA In Rs1.Fields
            a = a & "<" & FieA.Name & ">" & FieA.Value
        Next FieA
    End If
    Rs1.Close
    Set Rs1 = Nothing
    ado1 = a
End Function
```
記述方法の理解は後に回すとして、この段階では、接続文字列 Con にはデータベースへの接続に必要な情報、SQL 文字列には、接続したデータベース上で、目的のデータを検索するために必要な情報が、それぞれ記述されていると考えてください。この 2 つが、 ADO の Recordset Object を得るために必要な情報です。
関数 ado1 の内部では、引数でもらった 2 つの情報をそのまま、ADO Recordset Object の Open メソッドに渡しています。
```
    Rs1.Open SQL, Con, adOpenForwardOnly
```
Open メソッドでは、引数を 3 つ指定していますが、順に、SQL、接続文字列、カーソルタイプです。レコードセットを得る場合に使用する SQL は Select 文です。これは、使用するデータベースに合わせて記述します。接続文字列は、データベースの種類や物理的な所在地を特定するための情報で、使用する環境に依存したものになります。第三引数のカーソルタイプは、レコードセットを読むだけなのか、書きこむのか、といったような、操作の種類に応じたものを指定します。ここでは、読み込み専用でパフォーマンスのよい adOpenForwardOnly (0) を指定しています。

次のコードは、読み込んだレコードセット全体をセルに格納します。

Sub ado5()
    Const adOpenForwardOnly = 0
    Dim Source As String, Con As String
    Dim Rs1 As Object, FieA As Object
    Dim x As Range, y As Range
    Set x = ActiveCell
    Set y = x.EntireColumn
    Con = "DSN=db1;"
    Source = "Select * From Table1 WHERE ID Between 1 And 10"
    Set Rs1 = CreateObject("ADODB.Recordset")
    Rs1.Open Source, Con, adOpenForwardOnly
    For Each FieA In Rs1.Fields
        x.Value = FieA.Name
        Set x = x.Next
    Next FieA
    Set x = Intersect(x.Rows(2).EntireRow, y)
    Do Until Rs1.EOF
        For Each FieA In Rs1.Fields
            x.Value = FieA.Value
            Set x = x.Next
        Next FieA
        Set x = Intersect(x.Rows(2).EntireRow, y)
    Rs1.MoveNext
    Loop
    Rs1.Close
    Set Rs1 = Nothing
End Sub

ADO Recordset の読み込みに必要な、最低限の基本操作を一覧にしておきます。

コンポーネントを呼び出す  Set Rs1 = CreateObject("ADODB.Recordset")
レコードセットを取り出す  Rs1.Open SQL, Con, adOpenForwardOnly
最終レコードの時、真　　  Rs1.EOF
フィールドコレクション　  Rs1.Fields
フィールドオブジェクト　  Rs1.Fields("Name1")   名前で指定
フィールドオブジェクト　  Rs1.Fields(0)         番号で指定
フィールドを取り出す　　  For Each FieA In Rs1.Fields
フィールド名　　　　　　  FieA.Name
フィールドの値　　　　　  FieA.Value
次のレコードへ移動　　　  Rs1.MoveNext
レコードセットを閉じる　  Rs1.Close
　　　　　　　　　　　　  Set Rs1 = Nothing

以上のように、 ADO Recordset は外部データベースに依存した記述を Open メソッドだけで吸収することができるため、 ado1 のような、接続文字列を変えるだけで、様々な種類のデータベースを読む関数を作ることができます。

ADO Recordset に書きこむ

ADO Recordset への書きこみは、読み込みの操作を少し変更するだけで可能です。ポイントは 2 つあります。まず第一に、動的カーソルを持つレコードセットを開くことです。カーソルタイプとして adOpenDynamic (2) を指定し、さらにロックタイプとして adLockOptimistic (3) を指定します。動的カーソルは読み込み専用の静的カーソルよりパフォーマンスが劣ります。
```
    Rs1.Open Source, Con, adOpenDynamic, adLockOptimistic
```
書きこみはレコード単位で行います。新規追加、変更、削除、いずれの場合でも、操作を行った後、カーソルを次のレコードに移動する前に、 Update メソッドを打って、変更を確定させます。
```
    Rs1.Update
```

次のコードは、レコードを更新します。選択した最初のレコードのフィールド Hiduke に現在の時刻を書きこみます。

Sub ado6()
    Const adOpenDynamic = 2
    Const adLockOptimistic = 3
    Dim Source As String, Con As String
    Dim Rs1 As Object
    Con = "DSN=db1;"
    Source = "Select * From Table1 WHERE ID = 3"
    Set Rs1 = CreateObject("ADODB.Recordset")
    Rs1.Open Source, Con, adOpenDynamic, adLockOptimistic
    Rs1.Fields("Hiduke").Value = Now()
    Rs1.Update
    Rs1.Close
    Set Rs1 = Nothing
End Sub

次のコードは、レコードを追加します。新しく追加したレコードのフィールド Hiduke に現在の時刻を書きこみます。

Sub ado7()
    Const adOpenDynamic = 2
    Const adLockOptimistic = 3
    Dim Source As String, Con As String
    Dim Rs1 As Object
    Con = "DSN=db1;"
    Source = "Select * From Table1 WHERE ID = 3"
    Set Rs1 = CreateObject("ADODB.Recordset")
    Rs1.Open Source, Con, adOpenDynamic, adLockOptimistic
    Rs1.addnew
    Rs1.Fields("Hiduke").Value = Now()
    Rs1.Update
    Rs1.Close
    Set Rs1 = Nothing
End Sub

次のコードは、レコードを削除します。選択した最初のレコードを削除します。

Sub ado8()
    Const adOpenDynamic = 2
    Const adLockOptimistic = 3
    Dim Source As String, Con As String
    Dim Rs1 As Object
    Con = "DSN=db1;"
    Source = "Select * From Table1 WHERE ID = 3"
    Set Rs1 = CreateObject("ADODB.Recordset")
    Rs1.Open Source, Con, adOpenDynamic, adLockOptimistic
    Rs1.Delete
    Rs1.Update
    Rs1.Close
    Set Rs1 = Nothing
End Sub

ADO Recordset への書きこみに必要な、最低限の基本操作を一覧にしておきます。

レコードセットを取り出す  Rs1.Open Source, Con, adOpenDynamic, adLockOptimistic
新しいレコードを追加する  Rs1.Addnew
現在のレコードを削除する  Rs1.Delete
現在のレコードを更新する  Rs1.Update

動的カーソルを使えば、データベースの更新を手軽に行えますが、 ADO は各データベースに対して Native な更新手段を提供しているのではないことに注意しておく必要があります。代表例がオラクルですが、読み出しに使う静的カーソルは ADO と馴染むものであるのに対し、 ADO が書きこみ時に使う動的カーソルはオラクルにそぐわないもののため、レコードの書きこみ時には、注意が必要となります。こういった部分を気にするよりも、更新の時は ADO Connection で SQL を使う、と割り切った方が明快かもしれません。(予想外の動きをされるリスクを減らせます。)

ADO Recordset の Open メソッドに関連する、代表的な定数を一覧にしておきます。

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- DataTypeEnum Values ----
Const adChapter = 136

ADO がインストールされているフォルダーの中に adovbs.inc というテキストファイルがあり、その中に ADO で必要な定数が定義されています。

ADO の接続文字列 (Access)

いよいよ、 ConnectionString (接続文字列) の解説です。まず、データベースを Access の MDB ファイルに限定して、様々な接続文字列の記述ルールを試してみましょう。接続文字列だけを変えて試すために、最初に登場した関数 ado1 を利用します。

Sub ado2()
    Dim Source As String, Con As String
    Source = "select * from Table1 where c=6"
    ' Microsoft OLE DB Provider for Microsoft Jet
    Con = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\opt\access1.mdb;User ID=Admin;Password=;"
    MsgBox ado1(Con, Source)
    ' Microsoft OLE DB Provider for ODBC
    ' DSN を使う記述
    Con = "Provider=MSDASQL;DSN=db1;UID=Admin;PWD=;"
    MsgBox ado1(Con, Source)
    ' ODBC はデフォルトプロバイダーなので省略記述できる。
    Con = "DSN=db1;"
    MsgBox ado1(Con, Source)
    ' FileDSN を使う記述
    Con = "FileDSN=fdb1;"
    MsgBox ado1(Con, Source)
    ' Driver を使う記述
    Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
      "UID=Admin;PWD=;DBQ=C:\opt\access1.mdb;"
    MsgBox ado1(Con, Source)
    ' フォーマルな記述(1)
    Con = "Provider=MSDASQL;Extended Properties=""" & _
      "Driver={Microsoft Access Driver (*.mdb)};" & _
      "DBQ=C:\opt\access1.mdb;DefaultDir=C:\opt;Exclusive=0;" & _
      "FIL=MS Access;MaxBufferSize=512;PageTimeout=5;ReadOnly=1;" & _
      "UID=Admin;PWD=;"""
    MsgBox ado1(Con, Source)
    ' フォーマルな記述(2)
    Con = "Provider=MSDASQL;Persist Security Info=False;" & _
      "Extended Properties=""DSN=MS Access 97 Database;" & _
      "DBQ=C:\opt\access1.mdb;DefaultDir=C:\opt;DriverId=25;" & _
      "Exclusive=0;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;" & _
      "ReadOnly=1;UID=Admin;"""
    MsgBox ado1(Con, Source)
    ' UDL (Universal Data Link) File を使う記述
    Con = "File Name=C:\opt\db1.udl;"
    MsgBox ado1(Con, Source)
End Sub

Microsoft OLE DB Provider for Microsoft Jet
```
MsgBox ado1(
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\opt\access1.mdb;
   User ID=Admin;Password=;"
   , "select * from Table1 where c=6")
```
Microsoft Jet は、代表的な OLE DB コンポーネントです。これがインストールされていない場合は、Microsoft Universal Data Access 関連ダウンロードのページからダウンロードできます。 Provider= の部分に、使用する OLE DB コンポーネントを指示し、 Data Source= に、データベースの物理的な場所を示すための情報を与えます。Jet の場合は、MDB ファイルへの物理パスです。このように、複数のプロパティを ; (セミコロン)で区切って、1つの文字列にして渡すというのが、接続文字列の基本的な記述方式です。
Microsoft OLE DB Provider for Microsoft Jet (UDL File を使う記述)
```
MsgBox ado1("File Name=C:\opt\db1.udl;"
   , "select * from Table1 where c=6")
```
UDL ファイルを作成すれば、OLE DB プロバイダーへの接続文字列を手軽に扱えます。事前に接続テストの実行もできるため、記述ミスによるエラーを軽減できます。 UDL ファイルをカレントディレクトリー(コンポーネントと同じディレクトリー) に配置した場合には、上記の例より単純に、パスを省略して記述できます。
Microsoft のデータベース戦略は、DAO から ADO 、そして、接続プールを Transaction コンポーネントに委ねる、という方向へとシフトしています。これらの遷移に伴い、データベースを扱う場合に望ましいとされる記述方法も、どんどん変化してきています。現時点では、Native な OLE DB Provider が利用可能な場合には、 ODBC を経由せず OLE DB Provider を直接利用する方が好ましいとされており、上記のように、 UDL File を使う方法が、 Microsoft の戦略と合致するやり方になります。なお、 UDL ファイルは、複数のサーバーで共有することはできません。
```
[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\opt\Access1.mdb;
Persist Security Info=False
```
UDL ファイルは、上の例のような、接続文字列を記述したテキストファイルです。 Microsoft データリンクのプロパティ画面で作成することができます。
Microsoft OLE DB Provider for ODBC (DSN を使う記述)
```
MsgBox ado1("Provider=MSDASQL;DSN=db1;UID=Admin;PWD=;"
   , "select * from Table1 where c=6")
```
OLE DB から ODBC ドライバーに引き渡す方法を利用すれば、 ODBC で利用可能なデータベース資源がすべて利用できるようになります。上のコードは、ODBC のシステム DSN に登録されているデータソース名 db1 を利用するものです。ユーザー名の記述が User ID= でなく UID= になっていますが、DSN= 以降の接続文字列はすべて、 OLE DB ではなく ODBC 側で処理されます。
Microsoft OLE DB Provider for ODBC (ODBC はデフォルトプロバイダーなので省略記述できる。)
```
MsgBox ado1("DSN=db1;"
   , "select * from Table1 where c=6")
```
ADO のデフォルト OLE DB Provider は ODBC です。そのため、 Provider=MSDASQL の記述を省略することができます。システム DSN の登録時にユーザーID やパスワードを埋め込むこともできますから、最も簡単な記述は DSN= を指定するだけです。
Microsoft OLE DB Provider for ODBC (ODBC のファイル DSN を使う。)
```
MsgBox ado1("FileDSN=fdb1;"
   , "select * from Table1 where c=6")
```
システム DSN の代わりにファイル DSN を使う場合は、上のようになります。
Microsoft OLE DB Provider for ODBC (DSN を使わず、直接記述する。)
```
MsgBox ado1(
  "Driver={Microsoft Access Driver (*.mdb)};
   UID=Admin;PWD=;DBQ=C:\opt\access1.mdb;"
   , "select * from Table1 where c=6")
```
ODBC の DSN を利用せず、必要な情報をすべて、その場で与える場合には、上のような記述になります。 Driver= で ODBC ドライバーの名称を正確に記述し、 DBQ= でデータベースの物理的な所在を与えます。ODBC ドライバーにはもっと多くのパラメーターを渡すことができます。それらを指定する必要があれば、上の接続文字列はもっと長くなります。

Microsoft OLE DB Provider for ODBC (フォーマルな記述。)

MsgBox ado1(
  "Provider=MSDASQL;Extended Properties=""
   Driver={Microsoft Access Driver (*.mdb)};
   DBQ=C:\opt\access1.mdb;DefaultDir=C:\opt;Exclusive=0;
   FIL=MS Access;MaxBufferSize=512;PageTimeout=5;ReadOnly=1;
   UID=Admin;PWD=;"""
   , "select * from Table1 where c=6")
MsgBox ado1(
  "Provider=MSDASQL;Persist Security Info=False;
   Extended Properties=""DSN=MS Access 97 Database;
   DBQ=C:\opt\access1.mdb;DefaultDir=C:\opt;DriverId=25;
   Exclusive=0;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;
   ReadOnly=1;UID=Admin;"""
   , "select * from Table1 where c=6")

公式通りの記述をすれば、上のようにとても長い接続文字列ができあがります。 ODBC ドライバーのための設定は、 Extended Properties= に一括して与えます。これらを一まとめにするために、" (ダブルコーテーション) が利用されます。上のコードでは、文字列の中に入る " をエスケープするために二重に記述しています。

Provider= に引き渡す文字列は正確に記述しなければなりません。ここに入るのは、必ず、システムに登録されているクラス名です。従って、該当プロバイダーが利用可能かどうかを調べるには、レジストリーを確認するのが早道です。
ADO Recordset の Open メソッドには、接続文字列以外に、接続を確立した ADO Connection Object を与えることもできます。複数のレコードセットを何度も開く場合には、一つの Connection Object を使いまわす方が効率的です。

ADO の接続文字列 (ファイル用ODBCドライバー)

ODBC ドライバーには Text ファイルを扱うものや Excel Worksheet を扱うものがあります。これを使えば、ファイルに対してデータベースのようなアクセスができます。

Sub ado3()
    Dim Source As String, Con As String
    Source = "select * from Table1 where c=6"
    ' ODBC Excel Driver
    ' C:\opt\Book1.xls にある "Table1"という名前の Range を扱う。
    ' Souce = "A1:C3" というように Range の直接指定も可能。
    Con = "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\opt\Book1.xls;"
    MsgBox ado1(Con, Source)
    ' ODBC Text Driver
    ' C:\opt\Table1 というテキストファイルを扱う。
    Con = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\opt;"
    MsgBox ado1(Con, Source)
End Sub

Excel 用のドライバーでは、 DBQ= に Excel Workbook の所在地を与えます。 SQL 文では、テーブル名として Range の名称を与えます。対象 Range の先頭行にフィールド名があるものとして SQL 文を記述します。 SQL の代わりに、対象 Range のアドレスを A1:C3 というように与えても動きますが、 Select 文の中では、この記述方法は使えません。
テキストファイルでは、 DBQ= に、テキストファイルのあるフォルダー名を与えます。このフォルダー内にあるファイルを扱うことができます。 SQL 文では、ファイル名をテーブル名として利用します。デフォルトでは、先頭にフィールド名のある CSV ファイルを想定していますが、パラメーターとして、テキストファイルの解釈方法を与えることもできます。

ADO の接続文字列 (Oracle)

オラクルを扱う場合には、OLE DB と ODBC の 2 通りの方法があります。

Sub ado4()
    Dim Source As String, Con As String
    Source = "select * from Table1 where c=6"
    ' Microsoft OLE DB Provider for Oracle
    Con = "Provider=MSDAORA;Data Source=serverName;" & _
      "User ID=userName; Password=userPassword;"
    MsgBox ado1(Con, Source)
    ' ODBC
    Con = "Driver={Oracle ODBC Driver};" & _
      "UID=userName;PWD=userPassword;DBQ=serviceName"
    MsgBox ado1(Con, Source)
End Sub

ADO の接続文字列 (XML)

ADO 2.6 以降では、 XML をレコードセットとして扱うことができます。

Sub ado10()
    Const adOpenForwardOnly = 0
    Dim Source As String, Con As String
    Dim x As Range, Rs1 As Object
    Set x = ActiveCell
    ' Microsoft OLE DB Simple Provider
    ' XML ファイルを、入れ子の Recordset として返す。
    ' データソースは、ファイル名または URL を指定する。
    Source = "C:\opt\table1.xml"
    Source = "http://someurl/opt/table1.xml"
    Con = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl;"
    Set Rs1 = CreateObject("ADODB.Recordset")
    Rs1.Open Source, Con, adOpenForwardOnly
    ado11 Rs1, x
    Rs1.Close
    Set Rs1 = Nothing
End Sub

Function ado11(Rs1 As Object, x As Range) As Range
' ADO-XML レコードセットをセルに書き出す。
    Const adChapter = 136
    Dim FieA As Object, Rs2 As Object
    Dim i As Long
    i = 1
    Do While Not Rs1.EOF
        For Each FieA In Rs1.Fields
            If FieA.Type = adChapter Then
                Set Rs2 = FieA.Value
                Set x = ado11(Rs2, x(i + 1, 2))(2, 0)
                i = 1
                Set Rs2 = Nothing
            Else
                x(i) = FieA.Name
                x(i, 2) = FieA.Value
                i = i + 1
            End If
        Next
    Rs1.MoveNext
    Loop
    Set ado11 = x(i)
End Function

上のコードは、 XML ファイルを開いて、レコードセットのようにセル上に展開します。

ADO では XML を入れ子のレコードセットとして表現します。 DOM で見るのとは違う視点を XML に与えることができます。値を持たないタグは完全に無視されるため、厳密なツリー構造を得たい時には使えませんが、テーブルのような2次元構造を持つ XML をデータベースに持ちこみたい時には便利です。なお、日本語を扱う場合には UTF-8 を使用します。
Field Object の Type プロパティが adChapter (136) の時は、それが単一値のフィールドではなく、子ノードを持つことを示します。これが通常のレコードセットと大きく異なる点です。
もう一つ、 ADO XML の特徴は、 $Text という名称のフィールドが必ず存在するということです。この中には、 DOM の innerText と同様の情報、つまり、内側にあるすべての文字情報を合わせたものが入っています。

ADO Recordset - Database - Excel 97

はじめに

実例