Office Space: Microsoft Office アプリケーションのスクリプト作成に関するヒントとテクニック

Office Space へようこそ。Office Space は、Microsoft® Office アプリケーションのスクリプト作成に関するヒントとテクニックを紹介するコラムです。毎週木曜日に新しいヒントを掲載します。過去のヒントについては、Office Space アーカイブを参照してください。Microsoft Office でのスクリプト作成について質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。

Microsoft Access データベースからデータを読み取る

どの家庭にも、他の家族とはちょっと違う人がいるものです。良いとか悪いではなく、とにかく違うところがあるのです。ブルネットの一家に赤毛が混じっていたり、バーベキュー レストランを営む家族にベジタリアンがいたりという具合に。また、本の虫の一家に傑出したアスリートがいることや、仕事熱心な Scripting Guy がいることもあります。いずれにせよ、周囲から「この人、本当に妹さんなの。とてもそうは見えないけれど」と口にされる家族がどこの家庭にも決まって 1 人います。

Microsoft Office ファミリでそのような存在に当たるのは Microsoft Access でしょう。Word や Excel を筆頭に、他の Office アプリケーションはスクリプトを使用してプログラムからドキュメントにデータを追加できますが、ただ 1 つ、そのために使用するオブジェクト モデルやメソッドに互換性がないという難点があります。つまり、Word と Excel を使用する場合、面倒というほどではないのですが 2 種類のオブジェクト モデルを習得する必要があります。Word 文書にデータを追加する方法を知っていても、Excel ワークシートにデータを追加するときにはあまり役立ちません。

しかし Microsoft Access は違います。Access には独自のオブジェクト モデルがありますが、テーブルからの読み取りや書き込みなど、基本的なデータベース操作については、通常はそのオブジェクト モデルを使用する必要がありません。代わりに使用するのが ActiveX データ オブジェクト (ADO) です。これが大いに役立ちます。その理由は、ADO は何種類のデータベースがあっても使用できる標準のプログラミング モデルだからです。SQL Server データベースでも心配しないでください。Access を操作するコードは、大半の場合そのまま SQL Server でも機能します。Oracle データベースの場合も同じことです。標準の ADO コードは Oracle データベースへのアクセスにも使用できます。Visual FoxPro でも、DB2 でも、AS 400 でも、MySQL でも。そうです、ADO はここに挙げたもの以外にも多数のデータベースで使用できます。

以上の理由から、ADO を使用して Access データベースを操作する方法を中心に、時間を割いて皆さんに ADO を紹介しようと考えました。まず今回は基本として Access データベースに接続してデータを取得するところから始めます。その後、新しいレコードを追加する、既存のレコードを変更する、テーブルを作成および削除するなど、思いつく限りの他の作業について説明します。

まずは、Microsoft Access データベースからデータを読み取るスクリプトを見てみましょう。このスクリプトはファイル C:\Scripts\Inventory.mdb に接続し、GeneralProperties テーブルからデータを取得した後で、各レコードの ComputerName フィールドの値を返します。

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=c:\scripts\inventory.mdb" 

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
Loop

このスクリプト自体は短いことが見てわかります。このコードの大半は変更の必要がない定型コードです。コードの一部は暗号のようにも見えますが、心配する必要はありません。その部分の動作と動作する理由は、きちんと説明します。

心配しないでください。このコードには説明するだけの価値はあります。

このスクリプトの冒頭で 2 つの定数 adOpenStatic と adLockOptimistic を定義しています。それぞれの定数の値を 3 に設定しています。

定数 adOpenStatic はレコードセットの "カーソルの種類" を決定するために使用します。今の段階では、カーソルの種類で決定されることはレコードセットを操作する方法、およびデータベースの変更がレコードセットに動的に反映されるかどうかだと説明しておきます (つまり、データベースからデータを取得した後で、別のユーザーがデータベースにレコードを追加したとします。そのレコードはあなたが取得したレコードセットに反映されるでしょうか。それとも、再度データベースにクエリを実行して最新のデータを取得する必要があるのでしょうか)。

adOpenStatic カーソルを使用すると、レコードセットを順方向および逆方向に操作できますが、データベースの変更に合わせて自動的に更新されることはありません。このしくみだと、レコードセットをいったん取得した後は、データベースの変更を定期的に監視して "待機" する必要がないため、処理時間やネットワーク トラフィックを削減できます。

次の表に、カーソルの種類、定数、および値を示します。

もう 1 つの定数 adLockOptimistic はレコードの "ロックの種類" を構成するために使用します。ロックの種類は現在参照中のレコードを排他的に変更できるように、データベースがそのレコードをどのようにロックするか (またはロックが可能かどうか) を決定します。次の表に、ロックの種類、定数、および値を示します。

定数を定義した後は、ADODB.Connection オブジェクトと ADODB.Recordset オブジェクトを作成します。これらのオブジェクトは、名前のとおりの機能です。Connection オブジェクトはデータベース接続の管理と保守を受け持ち、Recordset オブジェクトはクエリの結果返されたデータを保存するコンテナとして使用します。この 2 つのオブジェクトは、次のコードを実行するだけで作成できます。

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

Connection オブジェクトが作成されたので、データベースへの接続を確立できます。今回のコラムではファイル パスを使用してデータベースにバインドします。後日、DSN (データ ソース名) を使用してデータベースにバインドする方法を説明します。以下に C:\Scripts\Inventory.mdb データベースに接続するコードを示します。

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=c:\scripts\inventory.mdb"

簡単ですね。Open メソッドを呼び出し、パラメータ Provider でプロバイダ名 (Microsoft.Jet.OLEDB.4.0)、Data Source でデータ ソース (ここではファイル パス) を渡しているだけです。コードを実行すると、Inventory.mdb データベースに接続できます。

接続を確立した後は、SQL クエリを発行してデータベースを操作します。まずは基本的なクエリから発行します。GeneralProperties テーブルのすべてのプロパティを選択します。そのための SQL クエリを次に示します。

SELECT * FROM GeneralProperties

次に、このクエリを Recordset オブジェクトと Open メソッドと組み合わせます。

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic

ここでは、4 つのパラメータを指定しています。

このコマンドを発行して取得したデータは、メモリの Recordset オブジェクトに格納されます。取得したコレクションを参照するには、MoveFirst メソッドを呼び出し、Do Until ループを設定してレコードセットのすべてのレコードを実際にループする必要があります。

MoveFirst メソッドは、カーソルをレコードセットの先頭に配置するために使用します。正直に言うと、このメソッドを記述する必要があるかどうかは定かではありません。おそらく自動的にカーソルはレコードセットの先頭に配置されるとだけ言っておきます。そうは言っても、転ばぬ先の杖です。

"カーソルをレコードセットの先頭に配置する" とは、文字どおりコレクションの最初のレコードを指すという意味です。カーソルはレコードセットでの位置を追跡するために使用します。ここでは、コレクションの操作を開始するときに、確実に先頭レコードから処理が開始されるようにしています。何かの理由でレコードセットの最後のレコードにアクセスする場合は、MoveLast メソッドを呼び出します。もちろん、レコードセットの任意のレコードをすぐに検索できる Find メソッドもあります。しかしそれらを使用するのは尚早です。

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC, ComputerName ASC

次に、Do Until ループを設定し、コレクション内のレコードに 1 つずつアクセスできるようにします。

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
Loop

スクリプト初心者はここでよく問題に遭遇します。そこで、いくつか注意点を挙げるのでメモしてください。

紙が行き渡るまでお待ちしますのでご安心を。すみませんが、鉛筆はそこの Scripting Guy が予備を持っていますが全員の分はありません。

よろしいでしょうか。まず、Do Until ループは objRecordset.EOF が True になるまで実行されます。EOF は "End Of File (ファイルの終わり)" の略です。したがって、レコードセットの先頭レコードから処理を開始し、レコードをすべてループするまで続行します。すべてのレコードを処理すると、自動的にループを終了します。

ここで注意することがあります。ADO では、アイテムがまったく格納されていないレコードセットを処理するとエラーが発生します。このエラーを無視するには、On Error Resume Next ステートメントを使用します。または、RecordCount プロパティの値 (レコードセット内のレコード数がわかります) を確認してから Do Until ループを開始する方法もあります。RecordCount が 0 の場合、レコードセットにはレコードがないので、Do Until ループを実行する意味がありません。RecordCount の値に基づいて処理を行うには、スクリプトを次のように変更します。

If objRecordset.RecordCount <> 0 Then
    Do Until objRecordSet.EOF
        Wscript.Echo objRecordSet.Fields.Item("ComputerName")
        objRecordSet.MoveNext
    Loop
End If

ここでもう 1 つ重要なポイントです。WMI や ADSI のスクリプトを記述した経験が豊富な方は、コレクションを処理するために相当な回数 For Each ループを使用したことがおありでしょう。For Each ループは優秀です。コレクションのすべてのアイテムを処理することを明示する必要がありません。たとえば、colServices コレクションをループしてすべてのサービスの名前を取得するために必要なコードはこれだけです。

For Each objService in colServices
    Wscript.Echo objService.Name
Next

Do Until ループだとこのようには行きません。しかし、For Each ループは取得したレコードセットを処理できないので、ここで行き詰まってしまいます。思い出してください。Do Until ループはレコードセットの各レコードを自動的にループしません。次のレコードに移動することを明示したコードをループ内に記述する必要があります。これが MoveNext メソッドの役割です。

objRecordSet.MoveNext

この 1 行を省略すると、何が起こるでしょうか。スクリプトは 1 行目のレコードから処理を開始します。Loop ステートメントに達すると、ループの最初に戻り、再び 1 行目のレコードを処理します。そして Loop ステートメントに達すると、ループの最初に戻り、再び 1 行目のレコードを処理します。永遠にその繰り返しです。つまり、ループの先頭に戻る前に MoveNext メソッドを呼び出すことが重要です。こうしないと、レコードセットのすべてのレコードにアクセスできません。

最後に、Do Until ループ内でデータベース フィールドを参照する方法について説明します。

Wscript.Echo objRecordSet.Fields.Item("ComputerName")

見てのとおり、フィールド名を直接指定するだけでは機能しません。

Wscript.Echo ComputerName

代わりにオブジェクトの完全パス objRecordSet.Fields.Item("ComputerName") を指定する必要があります。やや複雑に見えますが、大半は定型コードです。変更が必要な部分はフィールド名 Wscript.Echo objRecordSet.Fields.Item("ComputerName") のみです。この部分で若干の入力が必要ですが、フィールド名をエコーバックするためのコードと難しさは大差ありません。

ここで、コマンド プロンプトからスクリプトを実行すると、データベースに格納されたレコードに応じ、コンピュータ名の一覧を取得できます。

atl-ws-01
atl-ws-02
atl-ws-03
atl-ws-04
atl-ws-05

見事ではないですか。

今回はデータベースからコンピュータ名をいくつか読み取っただけか、ですって。確かに、あっと驚く展開はありませんでした。そのような展開にした一番の理由は、カーソルの種類とロックの種類を示す定数の意味、レコードセット内を移動する方法、レコードセット内のフィールドを参照する方法など、さまざまな基本を説明したかったためです。基本は習得できたので、ここからは楽しく作業できるでしょう。

間違いました、楽しくデータベースの作業ができるでしょう。期待を裏切らない成果が上がるはずです。

ページのトップへ