ADO (ActiveX Data Object)

概要

Windows DAC (旧MDAC) の一部
Connection#Openメソッドでデータソースに接続。このとき、データソースに合わせた接続文字列を渡す。
Connection#Executeメソッドなどでデータソースに問い合わせを行い、その結果をRecordSet型で受け取る
問い合わせ結果のレコードは、Fieldsコレクション型でRecordSet#Fieldsプロパティから取得できる。イテレータメソッド（EOFプロパティ、MoveNextメソッド）を使って必要なデータを取得する。

コードの例（JScript / WSH）

var con = WScript.CreateObject("ADODB.Connection");
con.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ノースウィンド 2007.accdb");
for(var rs = con.Execute("SELECT * FROM TABLE") ; !rs.EOF ; rs.MoveNext()) {
    for(var i=0;i<rs.Fields.Count;i++) {
        out += rs.Fields(i) + ((i<rs.Fields.Count-1)?"\t":"\n");
    }
}
con.Close();

接続文字列

Microsoft SQL Server

OLE DBプロバイダ	認証方法	接続文字列
SQLOLEDB (Microsoft OLEDB Provider for SQL Server)	Windows認証	Provider=SQLOLEDB;Data Source=サーバー名;Database=DB名;Integrated Security=SSPI
SQLOLEDB (Microsoft OLEDB Provider for SQL Server)	SQL Server認証	Provider=SQLOLEDB;Data Source=サーバー名;Database=DB名;User ID=認証ID;Password=認証パスワード
MSDASQL (Microsoft OLE DB Provider for ODBC)	Windows認証	Provider=MSDASQL;Driver=SQL Server;Server=サーバー名;Database=DB名;Trusted_Connection=yes
SQL Server Native Client \| Web

Access、Excel、テキスト

OLE DBプロバイダ	データソース	接続文字列
Jet (Microsoft Jet OLE DB Provider) \| MSDN	Access 2003	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ファイルパス
	Excel 2003	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ファイルパス;Extended Properties=Excel 8.0
	テキスト	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ディレクトリパス;Extended Properties=Text
Ace (Office 12.0 Access Database Engine OLEDB Provider)	Access 2007 / 2003	Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ファイルパス
	Excel 2008	Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ファイルパス;Extended Properties=Excel 12.0
	Excel 2003	Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ファイルパス;Extended Properties=Excel 8.0
	テキスト	Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ディレクトリパス;Extended Properties=Text
MSDASQL (Microsoft OLE DB Provider for ODBC) \| MSDN	Access	Provider=MSDASQL;Driver=Microsoft Access Driver (.mdb, .accdb);DBQ=ファイルパス
	Excel	Provider=MSDASQL;DSN=Excel Files;DBQ=ファイルパス Provider=MSDASQL;DRIVER=Microsoft Excel Driver (.xls);DBQ=ファイルパス*
	テキスト	Provider=MSDASQL;Driver={Microsoft Text Driver (.txt; .csv)};DefaultDir=ディレクトリパス

PostgreSQL、MySQL、SQLite、Oracle

ドライバ名	データソース	備考
PostgreSQL Native OLEDB Provider (PGNP) \| Web	PostgreSQL
MySQL Connector/ODBC \| Web	MySQL	64bit版あり
SQLite ODBC Driver \| Web	SQLite	サードパーティ製

その他

DB 以外のデータソースも ADO を使ったインターフェイスを持つ場合がある。

ドライバ名	データソース	接続文字列	備考
	Windows Search	Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;	Windows Vista 以降のデスクトップ検索ツール
OLE DB Provider for Microsoft Indexing Service \| MSDN	Microsoft Indexing Service	Provider=MSIDX;Data Source=System
Jet	Microsoft SharePoint	Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=”WSS;IMEX=2;RetrieveIds=Yes;DATABASE=SharePointのURL;LIST=リストなどのGUID“
Ace	Microsoft Sharepoint	Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=SharePointのURL;LIST=リストなどのGUID;

エクセルを読むときの注意

シート名、もしくは名前を付けた範囲が DB のテーブルに相当する。SQL 文の中でテーブル名を記述するときは、「[テーブル名$]」というように書く必要がある。
どうやら任意のセルの範囲をテーブルとして指定することもできるようだ。select * from [Sheet1$A1:C5];
こんな感じ。これは便利。
データソースとして存在しないエクセルファイルを指定すると、特にエラーが起きることなく開くことができる。この場合、まっさらな新しい DB が作られていて、create table などをすると、実際にそのファイル名で ADO がエクセルファイルを作る。
MSDASQL 経由で使う場合は、デフォルトが読み取り専用。書き込むのであれば、接続文字列に「ReadOnly=False」と付け加える必要がある。（*11）

テキストファイルを読むときの注意

接続文字列で指定するのは、ファイル名ではなく、ディレクトリ名であるのに注意。
ファイル名は、SQL 文の中にテーブル名として記述する。「select * from test.txt」みたいな感じ。
ファイル名に空白文字列が含まれている場合は、「[ファイル名]」もしくは「`ファイル名`」みたいな感じに囲む。（*7）
デフォルトでは、テキストファイルの一行目が列名として扱われる。
テキストファイルの一行目を列名として扱わない場合は、接続文字列の Extended Properties を「Extended Properties=”Text;HDR=NO“」みたいにする。この場合、列名としてF1、F2…F10…というものが自動的に割り当てられる。この仕様はエクセルでも同じ。
デフォルトの列区切りはカンマになっていて、タブ区切りや、あるいは固定長フィールドのテキストを読むときは、別途「スキーマ定義」を行う必要がある。

テキストファイルのスキーマ定義

MSDASQLでテキストファイルを読む場合、ときに、スキーマ定義（列区切りや列の型などの定義）を「Schema.ini」というファイル（MSDN）で指定することができるらしい。このファイルは、必ず「Schema.ini」という名前である必要があり、かつ、読み込むテキストファイルと同じフォルダに置く必要がある。

テキストファイルの列区切りを指定する

対応している列区切りは、以下の4種類

TabDelimited（タブ区切り）
CSVDelimited（カンマ区切り）
CustomDelimited（二重引用符以外の任意の文字）
FixedLength（固定長フィールド）

特に指定しなければ、デフォルト値として CSVDelimited が使われる。この値は、レジストリの「HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text」の Format に設定されている。

明示的に列区切りを指定する場合は、Schema.ini を以下のように作成する。

まず角カッコで設定対象のファイル名（ファイル名だけ）を囲み、続けて設定内容を記述する。

[タブ区切りのファイル.txt]
Format=TabDelimited
[アスタリスク区切りのファイル.txt]
Format=Delimited(*)
[固定長フィールドのファイル.txt]
Format=FixedLength

固定長フィールドを持つテキストファイルはどうする

文献9の Working with Fixed-Length Files の項目を参照。

テキストファイルの列の型などを指定する

その他の注意事項

文献8の Deprecated MDAC/WDAC Components などに書かれているが、Jet は非推奨のコンポーネントで、MDAC 2.6 以降には含まれず、64ビット版も提供されないとのこと。
ACE プロバイダーは、Office 2007 といっしょにインストールされるっぽい。単体でインストールするパッケージも Microsoft が配布している。
ACE プロバイダーの、Office 2010 対応版？のパッケージも Microsoft が配布している。
接続文字列の情報を集積した ConnectionStrings.com なるサイトがある。便利。

参考文献

ADO プログラマーズガイド <http://msdn.microsoft.com/ja-jp/library/cc408238.aspx>
ADO API リファレンス <http://msdn.microsoft.com/ja-jp/library/cc408215.aspx>
エクセルExcel大事典 VBAマクロ CSV ADODB Jet ODBC OLEDB MSDASQL HDR FirstRowHasNames <http://home.att.ne.jp/zeta/gen/excel/c04p47.htm>
Microsoft ODBC Desktop Database Drivers <http://msdn.microsoft.com/en-us/library/ms711711(VS.85).aspx>
Schema.ini File (Text File Driver) <http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx>
Hey, Scripting Guy! Weekend Scripter: Querying the Windows Search Index – Hey, Scripting Guy! Blog – Site Home – TechNet Blogs <http://blogs.technet.com/b/heyscriptingguy/archive/2010/05/29/hey-scripting-guy-weekend-scripter.aspx>
Hey, Scripting Guy! <http://www.microsoft.com/japan/technet/scriptcenter/resources/qanda/may05/hey0524.mspx>
Data Access Technologies Road Map <http://msdn.microsoft.com/en-us/library/ms810810.aspx>
Much ADO About Text Files <http://msdn.microsoft.com/en-us/library/ms974559>
ConnectionStrings.com – Forgot that connection string? Get it here! <http://www.connectionstrings.com/>
[HOWTO] Visual Basic または VBA から ADO を Excel データで使用する<http://support.microsoft.com/kb/257819/ja>

NYORO PRESS

Just another WordPress weblog