気の向くままに辿るIT/ICT/IoT

SQL用API

DAO/Data Access Objects データアクセスオブジェクト

DAO/Data Access Objects データアクセスオブジェクトとは？

DAO Data Access Objects

DAOとは

　DAOとは、Data Access Objectsの略でMicrosoft社が開発したデータベース接続(データベースアクセス)用プログラミングインタフェースであり、VB、VCなどMicrosoft製プログラミング言語から利用されますが、ローカル接続に長けており、主にAccess VBA/Excel VBA/VBで利用されます。

*ADO/ADO .NETへの移行推奨

DAOによるDBアクセス

　DAOは、Microsoft社のRDBアプリAccessのデータベース接続インタフェースでもあるJetデータベースエンジンを利用したMS Accessの.mdbファイルへの接続や、やはり当初MS社が開発、その後、オープン仕様となった汎用データベース接続インタフェースであるODBCを経由したRDBデータベースサーバへの接続、また、ExcelやSQL Server、dBASE/Paradox/Microsoft Oracle ODBCなどISAM/Indexed Sequential Access Methodを採用したDBや、更にはテキストファイルに直接接続することができます。

DAO参照設定

　DAOを利用する為には、AccessやExcel VBAやVB6.0では、GUIエディタのメニューから参照設定を行い、『Microsoft DAO ? Object Library』を指定します(「?」にはバージョンが入ります)。

　尚、VBやVBA、そのバージョンによって参照が異なる場合もありますし、選択基準も機能ごとだけでなくVB/VBAの異なるバージョンを利用する為に参照するケースもあり、他方、通常複数のライブラリが参照された状態が普通であり、ライブラリ相互に依存関係や場合によっては競合関係がある場合もあり、他の参照が必要、例外的に他の参照を外す必要が出てくる可能性もあります。

DAOのメソッド／プロパティ

object.method

object.property

　DAOのオブジェクトにおけるメソッド、プロパティは、オブジェクトに続けてドット、メソッド名またはプロパティ名と記述することによって指定または参照します。

DAOのオブジェクト階層

DBEngine

|_Errors

| |_Error

|_Workspaces

|_Workspace

|_Connections

| |_Connection

| |_QueryDefs

| | |_QueryDef

| | |_Parameters

| | | |_Parameter

| | |_Fields

| | |_Field

| |_Recordsets

| |_Recordset

| |_Fields

| |_Field

|_Databases

| |_Database

| |_QueryDefs

| |_Recordsets

|_Groups

| |_Group

| |_Users

|_Users

|_User

　かなり大ざっぱですが、DAOの階層におけるDBEngineの下位には、Workspaces、更にその下位にWorkspaceがあり、そのWorkspaceの下位には、Connectionsの下位にConnectionやDatabasesの下位にDatabaseオブジェクトがあり、更にその下層にRecordsets...と連なっています。

　英語表記における複数形のsがつくものは、各Object(オブジェクト)を包含するCollection(コレクション)と呼ばれており、特殊なケースを除き、普通はsのつかない通常のオブジェクトを記述し、使用します。

　また、Workspaces/Workspaceオブジェクトは多重に開く(多次元配列)ことができ、既定及び最初に開くWorkspaceはWorkspaces(0)で順次Workspaces(1)、Workspaces(2)、また、Workspaces(0)(0)、Workspaces(0)(1)と宣言することで各作業領域を確保することができる仕様になっていますが、既定でWorkspaces(0)として1つだけ開く(一般的な使用方法の)場合にはsの付かないDAO.Workspaceで記述するか、一切書かずに省略することが可能です。

　ちなみにこうした階層構造はツリー(木)構造と呼ばれることがあり、出発点である元(DAO階層ではDBEngine)を根っこに喩えてルート(根)と呼び、ルートに比し、使用頻度は格段に少なくなるとは思いますが、ルートからの分岐を枝や葉と呼ぶことがあります(尚、枝をブランチ/branchと呼ぶ場合もある一方、葉をリーフ/leafとする表現はあまり見聞きしない気がしますが、気のせい?)。

DAOによるRecordsetアクセス方法

　DAOを使ってRecordset(テーブルのデータ)にアクセスする方法は、Jet経由とODBC経由で若干異なります。

Jet経由によるDAOのデータアクセス／VB6.0

　Jet経由の場合には、Databaseオブジェクトによりデータベースに接続し、Recordsetオブジェクトによって接続したデータベース内のレコードにアクセスするという流れになります。

　AccessのMDBファイルにアクセスする際のVB6.0によるコードは、例えば、以下のように記述します。

Dim db As DAO.Database

Dim rs As DAO.Recordset

　Jet経由の場合には、DatabaseオブジェクトとRecordsetオブジェクトを使用するので、それぞれの型の変数を宣言します("DAO."の付加を推奨)。

　mdbを新規作成する場合には、DAOのオブジェクト階層のルートであるDBEngineのCreateDatabaseメソッドを使用します。

　既存のmdbを開く場合には、ルートであるDBEngineのOpenDatabaseメソッドを使用し、引数としてデータベース名(フルパス)を(必要に応じてuser_id/passwordも)渡します。

Set db = DBEngine.OpenDatabase(" db_name ")

　次にデータアクセスですが、ODBC/Jet何れを経由する場合もデータベース接続後、データ操作言語(DML/Data Manipulation Language)であるselect/insert/update/deleteの内、SQLの結果を取得するselectにはOpenRecordsetメソッド、取得しないinsert/update/deleteにはExecuteメソッドを使います。

　Setステートメントで(DAO.DatabaseのOpenRecordsetで)開いたレコードセット(select文の結果)をDAO.Recordsetに割り当てます。

Set rs = db.OpenRecordset(" SELECT * FROM table_name ")

　Recordsetが開いている間、select文の結果をSetしたDAO.Recordsetオブジェクト(例ではrs)に1レコードずつアクセスできるのでWhileループなどで各レコード(行)ごとに各データ(列)を個々に参照、取得することができます。

　各データ(列)は、レコードごとにリスト(配列)としてDAO.Recordsetオブジェクト(例ではrs)に格納されており、例ではrs(0)、rs(1)...やrs("col_name_A")、rs("col_name_B")...等、丸かっこ内に0から始まる列番号または列名(column/カラム名)を入れた添え字付きで参照することができます。

　尚、ループ中の行の移動に利用可否や利用可能な場合のメソッドは、DAOにおいては、DAO.WorkspaceオブジェクトのDefaultCursorDriverプロパティの値によって変わってきます。

' End Of FileがFalse(レコードの終わりじゃない間ループ)

while rs.EOF = False

...

Wend ' While end

' 行移動：rs.MoveFirst/rs.MoveLast/rs.MovePrevious/rs.MoveNext

' 列参照：

' rs(0)、rs(1)、rs(2)...と0から始まる列(column)番号

' またはrs(col_name)というカラム名でもアクセス可

　使い終わったら開いたDAO.Recordsetを閉じます。

rs.Close ' 開いてSetしたものは使い終わったら閉じる

　insert/update/deleteの場合、DAO.DatabaseのBeginTransでトランザクションを開始し、SQLは、DAO.DatabaseのExecuteメソッドで発行、DAO.DatabaseのCommitTransで確定、Rollbackで取り消します。

db.BeginTrans ' トランザクションの開始

db.Execute (" INSERT INTO table_name VALUES ( value1 , value2 , ...) ")

db.Execute (" UPDATE table_name SET ... ")

db.Execute (" DELETE FROM table_name WHERE ... ")

db.CommitTrans ' コミットすることでデータベースに反映される

' 何らかの判定後、処理を取り消す場合には、db.CommitTransではなく、

' db.Rollback

　ちなみに、Executeメソッドの引数を括る外側のカッコは省略も可能です。

db.Execute " INSERT INTO table_name VALUES ( value1 , value2 , ...) "

db.Execute " UPDATE table_name SET ... "

db.Execute " DELETE FROM table_name WHERE ... "

　尚、SQL文をあらかじめ変数にセットしてからExecuteメソッドに渡すこともできます。

Dim INS_Data As String

Dim UPD_Data As String

Dim DEL_Data As String

INS_Data=" INSERT INTO table_name VALUES ("

INS_Data=INS_Data + " value1 "

INS_Data=INS_Data + " , value2 "

INS_Data=INS_Data + " ) "

UPD_Data=" UPDATE table_name SET ... "

DEL_Data=" DELETE FROM table_name WHERE ... "

db.BeginTrans

db.Execute INS_Data

db.Execute UPD_Data

db.Execute DEL_Data

db.CommitTrans

　使い終わったら開いたDAO.Databaseを閉じます。

db.Close ' 開いてSetしたものは使い終わったら閉じる

　尚、DAO.Workspaceオブジェクトを利用する必要がある場合には、下記のようなコードを追加します。

Dim ws As DAO.Workspace

' または

' Dim ws As DAO.Workspaces(0)

...

Set ws = DBEngine.CreateWorkspace( "workspace_name" , & _

user_name , password , dbUseJet )

...

ws.Close

...

CreateWorkspace()メソッド

【第4引数】dbUseJetは、Jetデータベースエンジンを利用する場合の定数

同メソッドをODBCで利用する場合には、この引数はdbUseODBCです。

　というわけでJet経由のDAOによるアクセスは下記のようになります。

' 例）VB6.0 Jet経由

Sub DB_Open_via_Jet()

Dim db As DAO.Database

Dim rs As DAO.Recordset

...

' mdb新規作成

' Set db = DBEngine.CreateDatabase( & _

db_name , dbLangJapanese & ";pwd=NewPassword" )

' 既存のmdbを開く

Set db = DBEngine.OpenDatabase(" db_name ")

...

' select文の場合

Set rs = db.OpenRecordset(" SELECT * FROM table_name ")

while rs.EOF = False ' End Of FileがFalse(レコードの終わりじゃない間ループ)

...

Wend ' While end

rs.Close

' insert/update/delete文の場合

db.BeginTrans

db.Execute (" INSERT INTO table_name VALUES ( value1 , value2 , ...) ")

db.Execute (" UPDATE table_name SET ... ")

db.Execute (" DELETE FROM table_name WHERE ... ")

db.CommitTrans

' 取り消しはdb.Rollback

...

db.Close

' Set rs=Nothing

' Set db=Nothing

END Sub

CreateDatabase()メソッド

【第1引数】db_name：必須

MS AccessのMDBファイルのフルパス(上限255文字)

【第2引数】Connect：必須

照合用パスワードやlocale(国・地域*localではなくlocale)文字列を指定

例）dbLangJapanese & ";pwd=NewPassword"

【第3引数】Option：オプション：下記は設定値の選択肢

OpenDatabase()メソッド

【第1引数】db_name：必須

MS AccessのMDBファイル名またはODBCデータソースのDSN/Data Source Name

【第2引数】Option：オプション

True/False(排他モード/共有モード)

*既定はFalse：共有モード

仕様上、第2引数は様々なDB情報設定ができるとありますが...

【第3引数】readonly_flg：オプション

True/False(読み取り専用フラグ)

*既定はFalse：読み取り可／書き込み可

【第4引数】Connect：オプション

パスワードを含む様々な接続情報

ODBC経由によるDAOのデータアクセス／VB6.0

　ODBC経由のDAOのデータアクセスでは、DBEngineオブジェクト内にWorkspace(作業場所)を確保、そのWorkspace内でConnectionを開き(OpenConnectionメソッド)、ConnectionオブジェクトのOpenRecordsetメソッドでselect文を実行し、その結果をRecordsetオブジェクトにSet、結果セットが不要なInsert/Update/Delete文はWorkspaceオブジェクトのBeginTransメソッドで処理を開始、ConnectionオブジェクトのExecuteメソッドで発行、WorkspaceオブジェクトのCommitTransメソッドで確定／Rollbackメソッドで取り消しするという流れになります。

' 例）VB6.0 ODBC経由

Sub DB_Open_via_ODBC()

Dim ws As DAO.Workspace

Dim cn As DAO.Connection

Dim rs As DAO.Recordset

...

Set ws = DBEngine.CreateWorkspace( "workspace_name" , & _

user_name , password , dbUseODBC )

Set cn = ws.OpenConnection( "" , connect_opt , & _

readonly_flg , "odbc_connection_string" )

...

' select文の場合

Set rs = cn.OpenRecordset(" SELECT * FROM table_name ")

while rs.EOF = False

...

Wend

rs.Close

' insert/update/delete文の場合

ws.BeginTrans

cn.Execute (" INSERT INTO table_name VALUES ( value1 , value2 , ...) ")

cn.Execute (" UPDATE table_name SET ... ")

cn.Execute (" DELETE FROM table_name WHERE ... ")

ws.CommitTrans

' 取り消しはws.Rollback

...

rs.Close

cn.Close

ws.Close

' Set rs=Nothing

' Set cn=Nothing

' Set ws=Nothing

END Sub

CreateWorkspace()メソッド

【第4引数】dbUseODBCは、ODBCを利用する場合の定数

同メソッドをJetで利用する場合には、この引数はdbUseJetです。

OpenConnection()メソッド

【第1引数】""

カラで可

*複数開く場合の識別子として任意の文字列Connection1、Connection2などを設定

【第2引数】connect_opt ：下記は設定値の選択肢

【第3引数】readonly_flg

読み取り専用フラグ

True/False

【第4引数】：odbc_connection_string ：ODBC接続文字列

*下記の例は最少要件(例えばDATABASE=name;も付加可能)

*[ ]はオプション／セミコロンはセパレータ／指定時は全体をクォーテーションで括る

*"ODBC"は定数、小文字部分には該当する値を設定

*定数ODBC;以外は順不同

DAO.WorkspaceのDefaultCursorDriverプロパティ

　Jet経由におけるDAO.Database.OpenRecordsetまたは、ODBC経由におけるDAO.Connection.OpenRecordsetによって取得したSQL(select文)の結果であるレコードセットを参照、取得する為のループ中のレコード(行)の移動については、カーソルが利用可能か否か、利用可能な場合には、カーソルがサーバ側にあるのか、クライアント側にあるのかによって異なりますが、これらを設定するのがDAOの場合、DAO.WorkspaceのDefaultCursorDriverプロパティです。

...

Dim ws As DAO.Workspace

Dim rs As DAO.Recordset

...

Set ws = DBEngine.CreateWorkspace( "workspace_name" , & _

user_name , password , dbUseODBC )

' DAO.Workspaceのカーソルプロパティ

' ws.DefaultCursorDriver = dbUseDefaultCursor '-1

' ws.DefaultCursorDriver = dbUseODBCCursor ' 1

' ws.DefaultCursorDriver = dbUseServerCursor ' 2

' ws.DefaultCursorDriver = dbUseClientBatchCursor ' 3

' ws.DefaultCursorDriver = dbUseNoCursor ' 4

while rs.EOF = False

...

rs.MoveNext

Wend

rs.Close

...

　DAO.WorkspaceのDefaultCursorDriverプロパティを設定する場合には、当然と言えば当然ですがDBEngine.CreateWorkspaceでWorkspaceを生成した後に設定し(dbUseDefaultCursorの場合は省略可)、設定値とその意味は次の通りです。

' DAO.Workspace.DefaultCursorDriverプロパティ設定値

' dbUseDefaultCursor (デフォルトカーソルを利用)

' dbUseODBCCursor (ODBCカーソル(外部)を利用)

' dbUseServerCursor (サーバカーソルを利用)

' dbUseClientBatchCursor (クライアントカーソルを利用)

' dbUseNoCursor (カーソルなし)

' DAO.Recordsetのレコード移動用メソッド

' MoveFirst (先頭行へ移動)

' MoveLast (最終行へ移動)

' MovePrevious (前の行へ移動)

' MoveNext (後ろの行へ移動)

　ループ中のレコードセット内のレコード移動に利用する為のメソッドは、DAO.Recordsetオブジェクト(例ではrs)のこれら４つですが、全て利用できるのはDAO.Workspace.DefaultCursorDriverプロパティ設定値がクライアントカーソルの時だけで、それ以外で利用できる場合、その多くはMoveNextメソッドだけのようです。

　尚、例えばクライアントバッチカーソルを利用する(dbUseClientBatchCursorの)場合、予めレコードセットの全てをクライアント側で保持する必要があり、レコードセットのサイズが大きい場合には、それに応じて取得時のネットワーク上のトラフィック量が増え、メモリ上の格納領域も大きくなるということになります。

　また、例えばサーバカーソルを利用する(dbUseServerCursorの)場合、レコードセットのレコードを1行ずつ読み込む為、レコード件数(DAO.Recordset.RecordCount)は全てのレコードを読み込むまで知ることができませんが、dbUseClientBatchCursorの場合は、ループ前に把握することができます。

　DAOでODBCを経由する場合は、DAO.Workspace.DefaultCursorDriverプロパティ設定値には、dbUseODBCCursorを設定する必要があるので、DAOでその他プロパティを明示的に設定できるのは、実質Jet経由などODBCを利用しない且つWorkspaceを生成する時だけであり、逆に言うとカーソルを明示的に設定したい場合には、Jet経由(MS Accessの.mdbなど)において必然的にWorkspaceを確保する必要があるということになります。

　これらを勘案した上でレコードセットの操作方法を検討することになります。

Dim cn As New ADODB.Connection

　ちなみにMicrosoftはRDOやDAOに続き、後にADO/ActiveX Data Objectsというデータベース接続を開発・リリースした関係で型が重複するケースや紛らわしいケースを回避する意味も含め、宣言時、明示的にDAO.Workspace/DAO.Connection/DAO.RecordsetのようにDAO.を付加し、他方、ADOも"ADODB."と明示的に付加しADODB.Connectionとし、DAOとADOで重複する場合は必須、そうでない場合も付記することが推奨されています。

DAOオブジェクトとプロパティ

　DAOのオブジェクトは、ここで挙げた以外にも多々あり、各オブジェクトには、個々に多くのプロパティがありますが、ここでは割愛させて頂きます。

DAOの特長と歴史

　DAOはデータベースサーバへの接続というよりもAccess(.mdb/Microsoft Jet)の特にローカルでの接続、Microsoft Jetで直接読み込むことが可能なExcelやテキストファイル等々、やはりローカル利用に長けていることから、Access VBAやExcel VBA、クライアントサーバシステムのようにネットワークを介したシステムにおいては、とかくクライアントサイドのプログラミング言語であるVBで利用される傾向が強く、それに比し、稀にVC/VC++でも利用されることがあるという位置づけでした。

　データベースサーバへの接続は、間もなくリリースされたADOが推奨されましたが、データベースサーバ接続には長けているものの、当初DAOの機能を十分に取り込めなかった、おそらくより正確にはJetの代替技術が未完成、他方Jetとの連携は十分とは云えなかった為、その後しばらくADOとDAOは共存することになります。

　実際ADOのリリースからしばらく(Access 97辺りまで?)は、仕様としてADOによるMS Access mdbの更新(INSERT/UPDATE/DELETE)ができず、AccessにはDAO、その他にはADOを利用するようアナウンスしていましたが、後にDAOと同様にJetエンジンを介したADOによるAccess mdbの更新もできるようになっています。

　一方、システム構成としてクライアントもUI含めプログラミング言語で開発するクライアントサーバシステムというよりは、ブラウザをクライアントとするウェブシステムが主流となり、クライアントサイドのプログラミング言語がVBであるという発想や存在感は薄くなりましたが、その過程で.NET Framework技術が確立し、VB .NETでは、DAO/RDO/ADOの利用もできるもののADO .NETへのデータバインディングが可能であるのは旧ADOのみです。

　また、VCを包含するVC++ .NETでは互換性維持の為にMFCからDAOの参照は可能であるものの、それ以外でDAOを利用する機能はなくなりました(Express/無償版ではMFCの利用ができないので既にVisual VC++ Expressでは実質DAOは利用できません)。

　RDOとはODBCのCOMラッパであり、.NET以前のVBを強く意識して作られた経緯も相まって一部機能については高レベル(人間寄りの)言語VBで利用できて低レベル(機械寄りの)アクセスをも担うはずのVC++で利用できない機能もあるなど汎用性に欠ける部分もあるようです。

　これらのことからもMicrosoftは実質DAO、RDO技術で可能なことを包含し、シンプルに記述できる上により汎用性が高く、仕様が統一されていてActiveX機能によりウェブとの連携に長けているADO/ADO .NETを一層推奨し、ADO .NETへの集約したい意向のようですが妥当な選択でしょう。

2004-2008-someday