データベース1

プログラムの概要

オブジェクト型の変数宣言を行い、コード上で、データベ ースに接続し、ＳＱＬを発行する。 今回は、オブジェクト型の変数宣言に依り、データベース と接続する為のオブジェクト、及び、検索文を発行した際 にデータを格納するオブジェクトを、コード上で生成して データベースに接続し、各レコードのフィールド情報を取 得して、其れをコンボボックスの項目に追加する。此れに 依り、選択項目を、手入力では無く、コンボボックスのリ ストより選択する事が出来る様に成り、操作性が格段に向 上する。 亦、データ入力に依り、自動的に更新される可きデータを 自動で更新する事が出来る様にする。即ち、此処では、カ テゴリ別のデータ数を、自動的に更新する機能を付加する。

データベース操作プログラムⅡ

ＶＢ⑨ □ オブジェクト変数の宣言（Dim、New） □ プロパティの値の取得と設定（CursorLocation、RecordCount、Fields、EOF、ItemData） □ メソッドの利用（Open、Execute、Close、AddItem） □ ステートメントの利用（Call、Set） □ 組込関数の利用（Val） □ 制御構造構文（ループ Do ～ Loop） □ サブルーチン（新規プロシージャ） 今回の課題項目 □ オブジェクト型変数の宣言（Dim、New） □ ADODB.Connection（Open、Execute、Close） □ ADODB.Recordset（Open、Close、MoveNext、RecordCount、Fields、EOF） □ SQL 文（SELECT 文：レコードを検索する文、UPDATE 文：レコードを更新する文） 今回の重点項目 □ 様々な SQL 文を試してみる。 今回の応用項目

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 ラベル オブジェクト名 lblBookItem Index ０～４（コントロール配列） Caption ＩＤ、タイトル、価格、カテゴリ、著者 テキストボックス１ オブジェクト名 txtID Locked True Text 空白 テキストボックス２ オブジェクト名 txtTitle Text 空白 テキストボックス３ オブジェクト名 txtPrice Text 空白 コンボボックス１ オブジェクト名 cmbCate Text 空白 コンボボックス２ オブジェクト名 cmbWriter Text 空白 コマンドボタン オブジェクト名 cmdSQL Index ０～２（コントロール配列） Caption 追加、変更、削除 コンボボックス 選択肢の一覧を表示するコントロール。此の一覧より値を選択する事と、テキストボックスに 直接値を入力する事が出来る。 コマンドボタン ユーザーが選択したコマンド（命令）や操作を実行するコントロール。ＯＫボタンや実行ボタ ン等、処理の開始や中断・終了の合図に用いられ、最も良く利用されるコントロールです。 テキストボックス１ コマンドボタン コントロール配列 （０～２） ラベル コントロール配列 （０～４） テキストボックス２ テキストボックス３ コンボボックス１ コンボボックス２

■ プログラムリスト（追加分） ■ ' フォームが読み込まれた時の処理【一部追加】

Private Sub Form_Load( ) ' フォームのサイズを格納する MH = Me.Height MW = Me.Width ' サブルーチンコール Call CmbAddItem Call CateUpdate End Sub ' コンボボックスにアイテムを追加するジェネラルサブプロシージャ【追加】 Private Sub CmbAddItem( )

' サブプロシージャレベルでローカルな変数を宣言する Dim Cn As New adoDB.Connection Dim Rs As New adoDB.Recordset Dim SQL As String Dim I As Long ' カーソルサービスの位置を設定し、カーソルを開く Cn.CursorLocation = adUseClient Cn.Open adoDB.ConnectionString ' カテゴリ項目を取得し、コンボボックスに設定する SQL = "SELECT * FROM カテゴリ" Rs.Open SQL, Cn I = 0 If Rs.RecordCount > 0 Then Do While Rs.EOF <> True

cmbCate.AddItem Rs.Fields("カテゴリ名") & "" cmbCate.ItemData(I) = Val(Rs.Fields("ID") & "") Rs.MoveNext I = I + 1 Loop End If Rs.Close ' 著者項目を取得し、コンボボックスに設定する SQL = "SELECT * FROM 著者" Rs.Open SQL, Cn I = 0 If Rs.RecordCount > 0 Then Do While Rs.EOF <> True

cmbWriter.AddItem Rs.Fields("著者名") & ""

cmbWriter.ItemData(I) = Val(Rs.Fields("ID") & "") Rs.MoveNext I = I + 1 Loop End If Rs.Close ' オブジェクトとの関連付けを解除する Set Rs = Nothing Set Cn = Nothing End Sub データベースから空のデー タ取得した際、『Null』と謂 うデータと成るが、VB では ヌルを扱う事が出来ない為 空文字列『””』を接続する事 で変数等に代入する。 数値型のフィールドデータ を取得する際には、Val 関数 を使用し、数値以外の不正な データを少なくともゼロに する事が望ましい。 オブジェクト変数を宣言す る時に New キーワードを指 定すると、オブジェクトを最 初に参照した時に、オブジェ クトの新しいインスタンス が作成される。 コンボボックスにリストを 追加するには、AddItem メソ ッドを使用する。 コンボボックスでは、リスト 毎に番号を振る事が出来る。 此処では、ID フィールドの データを割り当てゝ居る。 オブジェクト型変数は使用 終了した時点で Nothing を 代入し、関連付けを解除する 事が望ましい。 頻繁に行う処理は、名前を付 け、一纏めに仕て置き、其の 処理を必要とする箇所より コール（呼出）する事で、対 象のコードを実行する事が 出来る。

' カテゴリ別データ数を更新するジェネラルサブプロシージャ【追加】 Private Sub CateUpdate( )

' サブプロシージャレベルでローカルな変数を宣言する Dim Cn As New adoDB.Connection Dim Rs As New adoDB.Recordset Dim Rs2 As New adoDB.Recordset Dim SQL As String Dim I As Long ' カーソルサービスの位置を設定し、カーソルを開く Cn.CursorLocation = adUseClient Cn.Open adoDB.ConnectionString ' カテゴリ別データ数を更新する SQL = "SELECT ID FROM カテゴリ" Rs.Open SQL, Cn If Rs.RecordCount > 0 Then ReDim Cate(Rs.RecordCount) For I = 1 To Rs.RecordCount

Cate(I).ID = Val(Rs.Fields("ID") & "")

SQL = "SELECT COUNT(ID) AS 合計 FROM 本" & _ " WHERE カテゴリ ID=" & Cate(I).ID

Rs2.Open SQL, Cn

If Rs2.RecordCount > 0 Then

Cate(I).Total = Val(Rs2.Fields("合計") & "")

SQL = "UPDATE カテゴリ SET トータル=" & Cate(I).Total & _ " WHERE ID=" & Cate(I).ID

Cn.Execute SQL End If Rs2.Close Rs.MoveNext Next I End If Cn.Close ' オブジェクトとの関連付けを解除する Set Cn = Nothing End Sub オブジェクト変数を宣言す る時に New キーワードを指 定すると、Set を使用してオ ブジェクトへの参照を代入 する必要は無い。 データベースのレコードを 取得し格納する変数の様に 配列の要素数を事前に決定 出来ない場合は、動的配列を 使用し、要素数が判明した時 点でReDim を使用して要素 数を指定する。 基本的にVB のコードは 1 行 毎でしか記述する事が出来 ないが、１行のコードが長い 場合は、空白に続き『アンダ ーバー』を使用する事で、複 数行を1 行と看做す事が出来 る。 ConnectionString プロパティ（ADODC） 直訳すると『接続文字列』。ダイアログボックスにて視覚的に設定する事が多いが、１字１句 間違わなければ、直接入力しても設定する事が出来る。要は、何のデータベースと接続するの かを指定するプロパティで有る。若し、フォルダの名称を変更したり、ファイル名を変更した り、保存場所を変更する等して、設定した時と異なる場合は、再度設定し直す必要が有る。 RecordSource プロパティ（ADODC） 指定した検索文を元に、ADODC オブジェクトは検索結果データを取得する。 データグリッドはADODC オブジェクト内に取得したデータを表示する。 Refresh メソッド（ADODC） RecordSource プロパティの指定のみでは、検索結果データは ADODC に取得され無い。 Refresh メソッドを実行する事に依り、データベース側の最新データを取得する事が出来る。 VB での SQL 文の扱い VB にて SQL 文を記述する際には、文字列(ダブルクオーテーション区切り)と成る。 データベースソフトに送られた時点で、文字列はSQL 文で有ると看做され、実行される。

ユーザー定義型とは ユーザー（プログラマー）が任意に定義するデータ型。Visual Basic がサポートする複数の データ型を組み合わせて、独自のデータ型を作成する事が出来る。 Type ユーザー定義型名 要素名１ As データ型 要素名２ As データ型 … End Type ユーザー定義型は、ランダムファイルアクセスやＡＰＩ関数呼出用構造体定義等、データ型 の異なる多数の関連する要素で構成されるデータレコードで良く使用される。亦、各要素に は、配列を指定する事も出来、静的配列と動的配列の孰れも使用する事が出来る。猶、データ型は、必 ず指定し、省略する事は出来ない。 宣言したユーザー定義型は、Visual Basic の他のデータ型と粗同様に使用する事が出来る。ユーザー定 義型で宣言された変数は、複数の要素（フィールド）を持つ事に成り、各要素の参照は、変数名の後ろ にピリオドに続き要素名を記述する。 Dim 変数名 As ユーザー定義型 （Public、Private でも宣言出来る） 代入変数／プロパティ = ユーザー定義型変数名.要素名 オブジェクト型変数の宣言 タイプライブラリで定義されたオブジェクトに対して変数を宣言する Dim 変数名 As [New] クラス名 オブジェクトをコードで使用する為に、対象のタイプライブラリに依り提供されるクラス名 を変数宣言の中で指定し、オブジェクトを変数に格納する クラス名は、コンポーネント名.クラス名（例:Exel.Chart）と謂う形式で構成される事が有る。 コンポーネント：オブジェクトを提供するコンポーネントの名前 オブジェクトブラウザの『プロジェクト／ライブラリ』ボックスに表示される。 ク ラ ス：コンポーネントのタイプライブラリを提供するオブジェクトのクラスの名前 オブジェクトブラウザの『クラス』ボックスに表示される。 New キーワードを使用してオブジェクト変数を宣言すると、オブジェクトが自動的に作成され、其の変 数を最初に使用する時に、オブジェクトの参照が代入される為、Set ステートメントを用いて変数への オブジェクトの参照を代入する必要が無い。 猶、New キーワードを用いて宣言した変数を使用すると、アプリケーションの実行速度が遅く成る可能 性が有る。此れは、Visual Basic では、New キーワードを用いて宣言した変数が有ると、其の度に、オ ブジェクトの参照が既に其の変数に代入されて居るか何うかが検証される為で有る。

亦、タイプライブラリとは、ＣＯＭ（Component Object Model：プログラムをソフトウェアコンポー ネントで構成する為のオブジェクト指向技術に基づくモデル）で利用可能な公開されて居るクラスに関 する情報を含むライブラリの事を謂い、プロパティーやメソッドの名前と、其等に付いての標準的な説 明が含まれる。 機 能 書 式 解 説 機 能 書 式 解 説

ADODC.Connection オブジェクト データベースと接続する為のオブジェクト Connection オブジェクトは、データ ソースへの接続を表し、データ ソースとの固有のセ ッションを表す。クライアントサーバーデータベースシステムの場合、此のオブジェクトは サーバーへの実際のネットワークに対応する。 ADODC.Connection の Open メソッド データソースへの接続を開くメソッド Connection.Open 引数１,引数２,引数３,引数４ データ ソースへの物理的な接続を設定する。此のメソッドが完了すると接続状態が確立し、 コマンドを発行して結果を処理する事が出来る。 引数１には、接続情報（ConnectionString）を指定し、省略する事が出来る。 引数２には、接続を確立する際に必要なユーザー名を指定し、省略する事が出来る。 引数３には、接続を確立する際に必要なパスワードを指定し、省略する事が出来る。 引数４には、同期接続、非同期接続の孰れかを示す定数を指定し、省略する事が出来る。 ADODC.Connection の Execute メソッド 指定されたクエリ、SQL ステートメント、ストアドプロシージャ等を実行するメソッド Connection.Execute 引数１,引数２,引数３ 指定した接続上で、引数１で指定されたメソッドに渡すクエリが実行される。 引数１には、実行するSQL ステートメント、テーブル名等を指定し、必ず指定する。 引数２には、プロバイダが返す操作の結果を反映するレコード数を指定し、省略する事が出来る。 引数３には、プロバイダが引数１を評価する方法を示す定数を指定し、省略する事が出来る。 ADODC.Connection の Close メソッド 開いて居るオブジェクト、及び、依存する総てのオブジェクトを閉じるメソッド Connection.Close Connection オブジェクト、又は、Recordset オブジェクトを閉じて、関連するシステムリソ ースを解放する。 オブジェクトを閉じてもメモリからは削除されず、プロパティ設定を変更してもう一度開く事が出来る。 メモリからオブジェクトを完全に削除するには、オブジェクト変数をNothing に設定する。 機 能 解 説 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

ADODC.Recordset オブジェクト 検索文を発行した際にデータを格納する為のオブジェクト Recordset オブジェクトは、基に成るテーブルからのレコードセット全体、又は、コマンド の実行に依り返された結果のレコードを表し、常にレコードセット内の１つのレコードのみ をカレントレコードとして参照する。亦、Recordset オブジェクトは、レコード（行）とフィールド（列） で構成される。Recordset オブジェクトは必要なだけ作成する事が出来る。 ADODC.Recordset の Open メソッド カーソルを開くメソッド Recordset.Open 引数１,引数２,引数３,引数４,引数５ ベーステーブルからのレコード、クエリ結果、又は、以前に保存されたRecordset を表すカ ーソルを開く事が出来る。 引数１には、実行するSQL ステートメント、テーブル名等を指定し、省略する事が出来る。 引数２には、有効なConnection オブジェクト変数名を指定し、省略する事が出来る。 引数３には、Recordset を開く際にプロバイダが使うカーソルタイプを指定し、省略する事が出来る。 引数４には、Recordset を開く際にプロバイダが使うロックの種類を指定し、省略する事が出来る。 引数５には、プロバイダが引数１を評価する方法を示す定数を指定し、省略する事が出来る。 ADODC.Recordset の MoveNext メソッド 指定のRecordset オブジェクトの次のレコードに移動して、其のレコードをカレントレコー ドにするメソッド Recordset.MoveNext カレントレコードの位置をRecordset の最後に向かい１レコード進める。 カレントレコードが最後のレコードの場合に MoveNext メソッドを呼び出すと、カレントレコードが Recordset の最後のレコードの後に設定され、EOF が True に成る。EOF プロパティが既に True の場 合、前方へ移動しようとするとエラーが発生する。 カレントレコードの位置を変更するには、他に下記のメソッドが有る。 MoveFirst Recordset の最初のレコードに移動する。 MoveLast Recordset の最後のレコードに移動する。 MovePrevious Recordset の先頭に向かい１レコード戻る。 前方スクロールのみ可能なレコードセットで両方向スクロールのサポートが必要な場合、CacheSize プ ロパティを使用してカーソルの後方スクロールをサポートするレコードキャッシュを作成し、Move メ ソッドを使用して移動する事が出来る。キャッシュされたレコードはメモリにロードされる為、必要以 上のレコードのキャッシュは避ける可きで有る。前方スクロールのみ可能なレコードセットで MoveFirst メソッドを呼び出す事は出来るが、其の結果、Recordset オブジェクトを生成するコマンド をプロバイダが再度実行する可能性が有る。 機 能 解 説 機 能 書 式 解 説 機 能 書 式 解 説

ADODC.Recordset の Close メソッド 開いて居るオブジェクト、及び、依存する総てのオブジェクトを閉じるメソッド Recordset.Close Connection オブジェクト、又は、Recordset オブジェクトを閉じて、関連するシステムリソ ースを解放する。 オブジェクトを閉じてもメモリからは削除されず、プロパティ設定を変更してもう一度開く事が出来る。 メモリからオブジェクトを完全に削除するには、オブジェクト変数をNothing に設定する。 ADODC.Recordset の RecordCount プロパティ Recordset オブジェクトに存在する現在のレコード数を示すプロパティ Recordset.Close Recordset オブジェクトに有るレコード数を調べる為に使用する。レコード数が解ら無い場 合、又は、プロバイダやカーソルの種類がRecordCoun をサポートして居ない場合は、プロ パティは－１を返す。閉じて居るRecordset 上で RecordCount プロパティを読み込むと、エラーが発 生する。 ADODC.Recordset の Fields プロパティ フィールドデータを示すプロパティ Recordset.Fields(引数) 引数で指定したフィールドデータの値を示す。 引数には、フィールド名を示す文字列を指定し、必ず指定する。 ADODC.Recordset の EOF プロパティ カレントレコードの位置が最後のレコードより後に有る事を示すプロパティ Recordset.EOF カレントレコードの位置が最後のレコードより後にある場合は True を返し、カレントレコ ードの位置が最後のレコード上、又は、其れ以前に有る場合はFalse を返す。 猶、BOF プロパティは、カレントレコードの位置が最初のレコードより前に有る場合は True を返し、 カレントレコードの位置が最初のレコード上、又は、其れ以降にある場合はFalse を返す。

BOF 及び EOF プロパティは、Recordset オブジェクトがレコードを含むか何うか、又は、他のレコー ドに移動した時にRecordset オブジェクトの限界を超えて居ないか何うかを調べる為に使用する。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

ADO では次の４種類のカーソルが定義されて居る。 １．動的カーソル：他のユーザーに依る追加、変更、及び、削除を見る事が出来、ブックマーク に依存しない、Recordset での所有る種類の移動を行う事が出来る。プロバイダがブックマ ークをサポートする場合は、ブックマークを使用する事が出来る。 ２．キーセットカーソル：動的カーソルと同じ様な属性を持つが、他のユーザーが追加したレコ ードを表示せず、他のユーザーが削除したレコードへのアクセスを許可し無い。他のユーザ ーが変更したデータは表示する事が出来る。常にブックマークが使用出来る為、Recordset での所有る種類の移動が可能で有る。 ３．静的カーソル：データの検索、又は、レポートの作成に使用する為の、レコードセットの静 的コピーを作成する。常にブックマークが使用出来る為、Recordset での所有る種類の移動 が可能で有る。他のユーザーに依る追加、変更、又は、削除を見る事は出来ない。クライア ント側(ADOR)の Recordset オブジェクトを開く場合に許可される唯一のカーソルタイプで 有る。 ４．前方スクロールタイプカーソル：レコードの前方スクロールのみ可能で有る点を除き、静的 カーソルと同じ属性を持つ。レコードセットを一度だけスクロールする必要が有る場合は、 此のカーソルに依り性能が向上する。 Recordset オブジェクトのカーソルタイプを選択するには Recordset オブジェクトのカーソルタイプを選択するには、Recordset オブジェクトを開く前に CursorType プロパティを設定する。亦、Open メソッドを使って CursorType 引数を引き渡す事 も出来る。プロバイダの中には、総てのカーソルタイプをサポートして居ない物も有る。プロバ イダのドキュメントを参照して確認する必要が有る。カーソルタイプを指定し無い場合、既定値 に依り前方スクロールタイプカーソルが開く。

Recordset オブジェクトのカーソルロケーションに付いて

Recordset オブジェクトを開く為に CursorLocation プロパティを adUseClient に設定すると、 Field オブジェクト上の UnderlyingValue プロパティは、返された Recordset オブジェクトでは 無効に成る。Microsoft SQL Server と連動する Microsoft ODBC Provider for OLE DB 等のプロ バイダを利用する場合、Open メソッドを使用して接続文字列を引き渡す事に依り、定義済みの Connection オブジェクトから独立した Recordset オブジェクトを作成する事が出来る。此の場合 もConnection オブジェクトが作成されるが、オブジェクトの値は変数に代入され無い。但し、同 じ接続で複数のRecordset オブジェクトを開く場合は、Connection オブジェクトを作成して開く 必要が有る。此の操作に依り Connection オブジェクトの値がオブジェクト変数に代入される。 Recordset オブジェクトを開く時に此のオブジェクト変数を使用しない場合は、ADO は、同じ接 続文字列を引き渡しても、新規 Recordset オブジェクトの夫々れに対して新規 Connection オブ ジェクトを作成する。 Recordset オブジェクトを開くと、カレントレコードが最初のレコードの位置に移動して、BOF とEOF プロパティの設定値が False に成る。レコードが存在しない場合は、BOF と EOF プロ パティの設定値がTrue に成る。

コード上でSQL 文を実行するには コード上でSQL 文を実行するには、コード上で、以下のオブジェクトを使用する。 ADODB.Connection オブジェクト（データベースと接続する為のオブジェクト） ADODB.Recordset オブジェクト （検索文を発行した際にデータを格納するオブジェクト） オブジェクトをコードで使用するには、変数に格納して使用する必要がある。 此れをオブジェクト型の変数宣言と謂う。

Dim 変数名 1 As New ADODB.Connection

Dim 変数名 2 As New ADODB.Recordset (SELECT 文で無い場合は不要)

INSERT・UPDATE・DELETE 文を実行する場合は、ADODB.Connection オブジェクトの Open(開く)→Execute(実行)→Close(閉じる)メソッドを使用する。 変数名 1.Open “データベースの種類・データベースの場所文字列“ 変数名 1.Execute “SQL 文“ 変数名 1.Close SELECT 文の場合は検索結果を取得するので、ADODB.Recordset オブジェクトに SQL 文を指定する。 変数名 2.Open “SQL 文“,変数名 1{ADODB.Connection} 「RecordCount」プロパティにて検索結果の件数を取得する事が出来る。 「Fields」プロパティにて指定したフィールドデータを取得する事が出来る。 「MoveNext」メソッドにて検索結果のレコードカーソルを移動する事が出来る。 変数名 2.Close ADODB.Recordset の EOF プロパティ レコードデータを参照する際には、カレントレコードを移動する必要が有る。Open メソッド実 行直後は検索結果の 1 番目のレコードがカレントレコードと成る。MoveNext メソッドを実行す る事に依り、1 件ずつカーソルを移動する事が出来る。EOF プロパティは最終レコードの下の意 味で「此れ以上はデータが無い」状況を謂う。 イメージ（検索結果データ） ID タイトル 1 ACCESS テクニック集 3000 2 EXCEL テクニック集 2500 8 実用 WORD 1300 ・ ・ ・ ・ 実行直後は1 件目にカーソルがある。 MoveNext メソッドを実行するとカーソルが 1 つずつ下へ移動する。 カーソルが 1 番下のレコードより下に移動す ると、EOF=True となる。