ADO：ACCESSとVBAとシステム開発：SSブログ

	ブログをはじめるログイン

ADOについて　その４　Recordsetオブジェクトのプロパティ　 [ADO]

Recordsetのプロパティについて。

　たくさんありますが、改めて確認してみると、よく使うのはEOFのみですね。

・BOF プロパティ
・EOF プロパティ
　現在読み出しているレコードが、レコードセットの先頭レコードより前、または最後のレコードの後に位置している場合、Trueになります。
　RecordsetをOpenして、全レコードを読み出す処理を書く場合、

Do While recA.EOF = False
/*処理いろいろ*/
recA.MoveNext
Loop

と、言う感じで、終わりを検出するのに使います。

以下、使いそうかな、と言う物。

2011-05-14 00:54 nice!(0) コメント(2) トラックバック(0)

ADOについて　その３　Recordsetオブジェクト　 [ADO]

　テーブルの内容をプログラムから参照したり、更新したりする時に使うのがRecordsetオブジェクトです。

　Recordsetオブジェクトは、テーブルの内容や、クエリの結果を、1レコードずつ参照することができます。仕組みをざっと図解すると･･･

　RecordSetオブジェクト自体に、データベースに問い合わせを行う機能はありません。データベースと会話をするのはConnectionオブジェクトの仕事なので、これを使ってどんなデータを取り出すかSQL文で指示を出します。
　データベースから返された結果は、ACCESSでテーブルやクエリをデータシートビューで見ているような状態になっています。この結果のデータがRecordsetと呼ばれる物で、プログラムからRecordsetオブジェクトを参照することで、データの内容を読み取ることができます。また、レコードの追加や変更も行えます。
　SQLの結果として返される内容は、図のような表の状態なのですが、Recordsetオブジェクトでは、この表を1行（ヨコの1行）ずつ読み出します。1行処理しては次の行へ進み、また処理が終わったら次の行の処理と繰り返します。

　これを実際にコードにすると、以下のようになります。

Public Sub subRecTest()

Dim Cn As New ADODB.Connection
Dim recA As New ADODB.Recordset
'DB接続
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.MDB;"
'レコードセットオープン
recA.Open "SELECT COMCD , COMMEI FROM TMSYOHIN ", Cn, _
adOpenForwardOnly, adLockReadOnly
Do While recA.EOF = False
Debug.Print recA!COMCD & "：" & recA!COMMEI
recA.MoveNext
Loop
'レコードセットクローズ
recA.Close
'DB切断
Cn.Close

End Sub

　この例では、「TMSYOHIN」というテーブルから、全レコードの「COMCD」「COMMEI」を読み出します。

　Recordsetオブジェクトの詳しい内容は、また別に説明します。
項目の値を取り出すときに、

Debug.Print recA!COMCD

という記述をしていますが、これは、

recA.Fields("COMCD").Value

を、略した書き方です。「!」について詳しく説明したいところですが、私はよくわかりません。ふ。

　Recordsetを使うと、テーブルのレコードをプログラムから直接操作できるわけですが、あまり多用すると、DBのメリットが無くなりますのでご注意を。特に更新系。
　ちょっと総合計がほしいからSELECTって言うのは良いのですが、複雑な更新処理だからRecordsetでプログラムからごりごりやるのは、パフォーマンスや処理の安定性が悪くなる場合があるので、避けた方が良いです。極力SQLでやって、複雑な処理が必要な場合は、まずストアドでの実装を検討した方が良いと思います。

　大ざっぱですが、概要はこんなところで。

　Recordsetオブジェクトのプロパティへ続く。

2011-05-13 00:54 nice!(0) コメント(0) トラックバック(0)

ADOについて　その２　Connectionオブジェクト　 [ADO]

　ADOはいくつかのオブジェクトで構成されていますが、私が普段使っているのは、次の2つです。

Connection
Recordset

　Connectionオブジェクトは、データベースへの接続を行います。Connectionオブジェクトに、接続先データベースの場所・ユーザID・パスワードなどの情報を設定することで、データベースにアクセスできる状態にします。

　接続を行うメソッドは、Openです。
書式は、
connection.Open ConnectionString, UserID, Password, Options

ConnectionString･･･接続文字列を設定します。必須。
UserID･･･接続先データベースのユーザIDを設定。省略可。
Password･･･接続先データベースのパスワードを設定、省略可。
Options･･･同期・非同期処理の選択。省略可。省略すると同期。普通は同期。

　ここで謎の言葉が現れます、「接続文字列」。私も未だにどう書くのが正しいのかわかりません。とりあえず、MDBに繋ぎたいときは、次のように書けば繋がるようです。

例）
Dim Cn As New ADODB.Connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.MDB;"
Cn.Close

　赤文字部分に、繋ぎたいMDBのパスを指定してやります。

　接続文字列について、ちょっとだけふれておきます。
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.MDB;"
接続文字列は、「項目名」「=（イコール）」「値」区切りの「;（セミコロン）」を、複数持った文字列になっています。
　最初の「Provider」は、接続先データベースの種類を設定しています（正確には、接続に使用するドライバ）。
　二つめの「Data Source」は、データベースの場所、接続先の識別情報を設定しています。
　こんな感じで、接続に必要な項目を指定するようになっています。どんな項目を指定するかは、接続するデータベースで変わってきます。

　ACCESS自身もConnectionで接続を行っていて、
Application.CurrentProject.Connection
に、このConnectionオブジェクトがあります。
　「ConnectionString」プロパティで、接続文字列が参照できます。

Debug.Print Application.CurrentProject.Connection.ConnectionString

Provider=Microsoft.Jet.OLEDB.4.0;User ID=XXXXX;Data Source=C:\TEST.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

　という感じで、接続文字列には様々は項目が存在しています。が、普通に繋ぐ分には上記例の内容でも十分です。

　参考までに、
Oracleに繋ぐときの接続文字列
"Provider=OraOLEDB.Oracle;Data Source=SID;User ID=USER;Password=PASSWORD;"

SQLSERVERに繋ぐときの接続文字列（SQLSERVER認証）
"Provider=SQLOLEDB;Network Library=dbmssocn;Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USER;Password=PASSWORD;"

　赤文字部分は各々適宜変えて下さい。
Oracleは、だいたい上記の例で繋がると思います。SQLSERVERは、クライアントのMDACバージョンで、上記の例では繋がらない場合があるかも。SQLSERVERのバージョンでも違うことが。SQLSERVERは結構苦労した記憶が･･･。SERVERNAMEには、SQLSERVERが実行されているコンピュータ名とかIPアドレスを指定します。DBNAMEには、データベース名（？）を。DB作ったことある人ならわかるはず（？）。Network Library=dbmssocnはよくわからないけど、とりあえず付けてます（TCPで接続する指定らしい･･･）。

（20080814追記
　SQL Server 2005 Express Editionに対する接続は、上記の書き方では繋がらないようです。Native Client OLE DB（SQLNCLI）を使うようにしたら、繋がりました。あと、Data Sourceの書き方がサーバ名￥SQLEXPRESSになるようです。
　まだよく調べてないのですが、Express Editionはちょっと特殊なようです。）
（20110525追記
　上記のExpressの件ですが、インストールするときの途中の指定でこうなることがあるみたい。具体的にはまた今度確認してみます。）

　別タイプの接続文字列もあって、ODBC設定のDSN名を使用します。ヘルプにサンプルが載っています。このタイプで接続を行った場合、私の経験上（3～5年前の事）動作が不安定になることが多かったです。なのでこっちのタイプは使っていません。

　以下、主要なプロパティ、メソッドについて。
詳細はVBAのエディタで、単語を入力してF1キーを押してください。

・プロパティ

CommandTimeout プロパティ
　SQL文を実行した時に、処理結果が帰ってくるまで待機する時間を設定します。設定した時間までに応答が無い場合は、タイムアウトエラーになります。秒単位で指定します。
　規定値は30秒が設定されているようです。そこそこの規模のDBになると、処理によっては結構時間がかかるようになってくるので、10分（600秒）くらいを常に設定しておくのが無難です。無制限にするのはおすすめしません。

ConnectionString プロパティ
　さっきの接続文字列が参照できます。（設定もできる）

ConnectionTimeout プロパティ
　先のCommandTimeoutと似ていて紛らわしいですが、こちらはconnection.Openを実行して、DBから接続確立の返事が返ってくるまでの待ち時間です。
　通常は設定しなくても大丈夫だと思いますが、Openでエラーが起きる場合は、調整が必要になるかもしれません。

CursorLocation プロパティ
　小難しいことをしない限り意識する必要は無いのですが、ADOにはカーソルサービスという、動作状態を表すものがあります。
　クライアントカーソル（adUseClient）とサーバーカーソル（adUseServer）の二つの状態があり、動作が変わってきます。
　余談ですが、OracleでFOR UPDATE NOWAITを使ってロックをかけようとしたら、adUseClientにしないとうまくいかないことがありました。

State プロパティ
　Connectionの状態を確認できます。

・メソッド

Open メソッド
　データベースへ接続します。

Close メソッド
　データベースへの接続を解除します。
データベースの処理が終わったら、closeします。

Execute メソッド
　SQL文を実行します。
結果を受け取る必要のないSQL文は、これで実行します。
Insert、Update、Delete、あと、ストアドの実行とか。
　Selectは結果を受け取る必要があるので、RecordSetを使用します。

BeginTrans メソッド
CommitTrans メソッド
RollbackTrans メソッド
　トランザクション処理の制御を行います。
トランザクションの詳細はまた今度っ。

　次回はRecordsetオブジェクトです。

2011-05-12 00:54 nice!(0) コメント(0) トラックバック(0)

ADOについて　その１　概要　 [ADO]

　ADOの前に、ACCESS（のデータベース）が、どの様な仕組みで動いているかをざっと説明しておきます。

　ACCESS＝データベースそのもの、というふうに理解している人は、けっこういらっしゃるかと思います。実は違うんです。
　ACCESSのプログラムを起動したときに動いているのは、「MSACCESS.EXE」というプログラムですが、このプログラム自体に、データベースの機能は含まれていません。
　データベースにデータを登録したり、登録されたデータを検索して取り出したりする機能を持つプログラムのことを、データベースエンジンとか、DBMS（database management system）とか呼びます。ACCESSが使っているデータベースエンジンは、MSACCESS.EXEとは別のところで実行されていて、「Jet Database Engine」と言います。これがACCESSデータベースの中核です。（OracleやSQLServerもそれぞれ独自のデータベースエンジンを実装しています。）

　Jet自体は、ACCESS製品の付属品というわけではなく、Windowsに最初から実装されています。いくつかバージョンがあり、MicroSoftのサイトから「MDAC」と言う名称のモジュールでダウンロードできます。これにはJet以外にも、Windows上のデータベース関連のプログラムがまとめられています（ADOも含まれます）。
　ちょっと話がそれますが、MDBファイルの中に入っているデータにアクセスするには、JetとADOがあれば可能で、必ずしもACCESSがそのパソコンにインストールされている必要はありません。プログラムはVBのEXEでVBランタイムと共にセットアップして、データはMDBファイルに格納する、といった構成が可能なのです。ACCESSを買わなくて良いのでお得です。MDBのデータを見たいときは、MDBファイルをACCESSの入ったパソコンへ持って行けば良いし。

　で、MSACCESS.EXEですが、ここにあるのは、テーブルデザイン・クエリデザインのツール、フォームやレポートなどのユーザインタフェース、VBAやマクロによるプログラム環境などです。まあ、全部ユーザインタフェースと言うか、表層機能の集まりです。

　そしてやっとADO。
MSACCESS.EXEは、データの登録処理や、取り出し処理を、Jetへ依頼します。このMSACCESS.EXEとJetの間を取り持つプログラムが、ADOです（ADO以外もありますが）。正式名称は「ActiveX Data Objects」。データベースを操作するための機能が、ADOによって提供されています。ちなみに、こういう何かと何かの間を取り持つものを、インターフェースと呼びます。
　MSACCESS（または誰かが作ったプログラム）からJetに直接会話をすれば、ADOは要らないことになります。会話するのがJetだけならそれでもよいかもしれませんが、OraceやSQLServerなど他のデータベースと会話するには、それぞれの会話のルールをプログラマが習得する必要が出てきます。ADOはそういった個々のデータベースの違いを吸収して、接続先がどのデータベースでも、プログラマに共通の操作を提供しています。（データベースによって実装されている機能が違うので、それぞれのデータベースについて理解しておくことは必要です。）
　例えば、テーブルにレコードを追加する、という処理を、Oracleに対して行う場合と、SQLServerに対して行う場合のコーディングは、いくつかのパラメタが異なるだけで、処理手順はほぼ同じです。

　以上を図解すると、

　の、ような感じ。ACCESSはADOやDAO使い、Jetを経由して実際のデータにアクセスしています（と、思う）。ACCESS以外でも、ADOを使用できるプログラム言語なら、同様の方法でデータベースにアクセスすることができます。
　ADOはJet以外にも、OracleやSQLServer等のデータベースにアクセスすることができます。図には書いていませんが、実際にはADOから延びる赤い矢印部分に、ODBCドライバとか、OLEDBドライバとか言うやつが居て、ADOはこれらを呼び出して各DBにアクセスしていると思われます。この辺はちょっと資料が見つからなくて、想像ですのでご注意を。
　蛇足ですが、ODBCドライバや、OLEDBドライバは、各データベース製品に付属の物で、個別にインストールしてやる必要があります。ACCESS（Jet用）や、SQLServer用の物は、最初っから入っていて、特に何もせずに繋ぐことができます。Oracleは、OracleClientをインストールするときにインストールされます。
　OracleClientをインストールしていない状態でも、ODBC設定の画面に「Microsoft ODBC for Oracle」というのが表示されますが、これを設定してもOracleClientがインストールされていないと機能しませんのでご注意を。紛らわしいですね、これ。