トップ ＞ スキル ： アプリケーション ＞ VBA for Access ＞ 応用編（データベースオブジェクト DAOとADO）

VBA_Access

ADO

レコードセットの作成

データベース内に存在するテーブルやクエリのレコードデータを参照するには、RecordsetオブジェクトのOpenメソッドを使用します。参照だけではなく、レコードの更新も可能です。

◆ Openメソッド

【書式】

Recordset.Open ソース,接続データベース名,カーソルタイプ,ロックタイプ,オプション

  • ソース：テーブル名、クエリ名、SQLステートメントの記述
  • 接続データベース名：接続しているConnectionオブジェクト名
  • カーソルタイプ：カーソルとはレコードセットをVBAで操作を可能にする機能
  • ロックタイプ：ロックとはレコードセットを開いたユーザ以外のユーザの制御する機能
  • オプション：引数のソースを評価する方法（Commandオブジェクト以外のソースの場合）
カーソルタイプ
タイプ 説明
adOpenStatic レコードセットのすべての方向に移動することができるが、他のユーザが更新したレコードは参照できない。
adOpenDynamic レコードセットのすべての方向に移動することができ、他のユーザが追加、更新、削除したレコードも参照できる。
adOpenKeyset レコードセットのすべての方向に移動することができ、他のユーザが更新したレコードは参照できるが、追加、削除したレコードは参照できない。
adOpenForwardOnly レコードセットの先頭から後方に向かって移動でき、参照だけなら最も高速で動作する。（既定値）
ロックタイプ
タイプ 説明
adLockReadOnly レコードの編集、削除はできない。（既定値）
adLockPessimistic レコードごとに排他ロックを行う。レコードの編集を開始するとロックが始まり、編集を保存、またはキャンセルするまでは他のユーザはレコードの読み取り、編集ができない。
adLockOptimistic レコードごとに共有ロックを行う。Updateメソッドでレコードを更新する時に、そのレコードのみをロックする。
adLockBatchOptimistic 複数のレコードをバッチ更新できる。

ここでは、以下のような「T_売上」テーブルのレコードセットを操作することを前提に操作を行います。

《 記述例 》

T_売上テーブルの全レコードをレコードセットに取り込んでメッセージとして表示するには、次のように記述します。

	Sub レコードセット作成()
		Dim cn As ADODB.Connection
		Dim rs As ADODB.Recordset

		Set cn = CurrentProject.Connection
		Set rs = New ADODB.Recordset

		rs.Open "T_売上", cn

		MsgBox rs!売上日 & ":" & rs!商品名 & ":" & rs!金額 & ":" & rs!区分

		rs.Close: Set rs = Nothing
		cn.Close: Set cn = Nothing
	End Sub

動作を確認すると、1件のレコードが表示されます。

この記述方法では、Accessが判断した順位の先頭のレコードのみが返されます。よって、全レコードを同時に返すことはできません。

全レコードを表示

レコードセットは同時に複数のレコードを参照することができない制約があるので、全レコードを表示させたいのであれば、Loop機能を用いる必要があります。

《 記述例 》

先ほどのプロシージャを以下のように書き換えます。

	Sub レコードセット作成()
		Dim cn As ADODB.Connection
		Dim rs As ADODB.Recordset
		Dim strData As String

		Set cn = CurrentProject.Connection
		Set rs = New ADODB.Recordset

		rs.Open "T_売上", cn, adOpenForwardOnly, adLockReadOnly

		Do Until rs.EOF
			strData = strData & vbNewLine & rs!売上日 & ":"
			strData = strData & rs!商品名 & ":" & rs!金額 & ":" & rs!区分
			rs.MoveNext
		Loop

		MsgBox "T_売上テーブルの全レコード" & vbNewLine & strData

		rs.Close: Set rs = Nothing
		cn.Close: Set cn = Nothing
	End Sub

動作を確認すると、テーブル内のすべてのレコードがメッセージとして表示されます。

並び替えの指示を設定していないので、Access自身が判断した順序で表示されます。
何故、「2010/02/05：DVDドライブ：6800：記憶」が先頭に表示されるかを知るには、T_売上テーブルにオートナンバー型のフィールドを設定します。
するとオートナンバー型の番号順にVBA実行時では全レコードが上から順番に表示されていることが分かります。

これは、このレコードが入力された順序をAccessは判断して順番を決めているからです。

並び替えを指定して表示

売上日の順番に売上データを表示したいのであれば、選択クエリを作成して、そのクエリを基にしてレコードセットを作成します。

あらかじめ、選択クエリとして「Q_売上」を作成しておきます。

《 記述例 》

選択クエリで売上日を昇順で設定したので、先ほどのプロシージャ内のOpenメソッドで指定したテーブル名をクエリ名に書き換えます。

	Sub レコードセット作成2()
		Dim cn As ADODB.Connection
		Dim rs As ADODB.Recordset
		Dim strData As String

		Set cn = CurrentProject.Connection
		Set rs = New ADODB.Recordset

		rs.Open "Q_売上", cn, adOpenForwardOnly, adLockReadOnly

		Do Until rs.EOF
			strData = strData & vbNewLine & rs!売上日 & ":"
			strData = strData & rs!商品名 & ":" & rs!金額 & ":" & rs!区分
			rs.MoveNext
		Loop

		MsgBox "T_売上テーブルの全レコード" & vbNewLine & strData

		rs.Close: Set rs = Nothing
		cn.Close: Set cn = Nothing
	End Sub

動作を確認すると、選択クエリが基になったレコードセットを表示するため、売上日の昇順で並び替えられて表示されます。

SQLを用いてレコードセットを作成

SQL文を用いてレコードセットを作成すると、選択クエリを作成することなく並べ替えも指定できます。

《 記述例 》

テーブル「T_売上」からSQL文を使用してレコードセットを作成するには、以下のように記述します。なお、売上日を昇順に指定した記述にしています。

	Sub レコードセット作成3()
		Dim cn As ADODB.Connection
		Dim rs As ADODB.Recordset
		Dim strData As String
		Dim mySQL As String

		Set cn = CurrentProject.Connection
		Set rs = New ADODB.Recordset

		mySQL = "SELECT * FROM T_売上 ORDER BY 売上日"

		rs.Open mySQL, cn, adOpenForwardOnly, adLockReadOnly

		Do Until rs.EOF
			strData = strData & vbNewLine & rs!売上日 & ":"
			strData = strData & rs!商品名 & ":" & rs!金額 & ":" & rs!区分
			rs.MoveNext
		Loop

		MsgBox "T_売上テーブルの全レコード" & vbNewLine & strData

		rs.Close: Set rs = Nothing
		cn.Close: Set cn = Nothing
	End Sub

動作の結果は、並び替えを指定して表示したメッセージと同じになります。

編集可能できるレコードセットを作成

レコードセットを編集が可能な状態にするには、Openメソッドの引数を編集可能な定数を指定する必要があります。そして、RecordsetオブジェクトのUpdateメソッドを使用して更新させます。
ただし、編集したいレコードの位置をカレントレコードとして移動させなければ、Updateメソッドを用いてレコードを編集することはできません。

◆ Updateメソッド

【書式】

Recordsetオブジェクト.Update フィールド名,代入する値

※ カレントレコードを移動するには、Move系メソッドやFindメソッドを使用します。

《 記述例 》

テーブル「T_売上」の[金額]が10,000円未満であれば税込金額に更新するには、以下のような記述をします。ただし、更新させる場合はユーザに更新することを明示させる仕組みを施す必要があります。

	Sub レコード更新()
		Dim cn As ADODB.Connection
		Dim rs As ADODB.Recordset
		Dim strData As String
		Dim po As Integer

		Set cn = CurrentProject.Connection
		Set rs = New ADODB.Recordset

		rs.Open "T_売上", cn, adOpenKeyset, adLockOptimistic

		po = MsgBox("10,000円以上の金額を税込金額にします" & vbNewLine & _
			"よろしいですか？", vbOKCancel, "確認")

		If po = vbOK Then
			Do Until rs.EOF
				If rs!金額 > 10000 Then
					rs.Update "金額", Fix(rs!金額 * 1.05)
				End If
				strData = strData & vbNewLine & _
					rs!売上日 & ":" & rs!商品名 & ":" & rs!金額
				rs.MoveNext
			Loop
		Else
			Exit Sub
		End If

		MsgBox "10,000円以上は消費税込金額" & vbNewLine & strData

		rs.Close: Set rs = Nothing
		cn.Close: Set cn = Nothing
	End Sub

動作を確認すると、まず更新することを伝えるメッセージを表示します。更新させたくないのであれば[キャンセル]ボタンを押すと処理はそのまま終了します。更新するのであれば、[OK]ボタンをクリックします。

10,000円未満の金額には5％の税を課した金額が表示され、テーブルのデータに直接変更が反映されます。

※ Updateメソッドを呼び出す前に、追加または編集を行っているレコードから移動すると、自動的にUpdateメソッドが呼び出され変更が保存されるので、次のような記述もできます。

rs.Update "金額", Fix(rs!金額 * 1.05)
　　　　　↓
rs!金額 = Fix(rs!金額 * 1.05)
rs.Update

BOF、EOFプロパティ

ADOのRecorsdetオブジェクトにも、DAOと同様にBOFとEOFのプロパティが用意されています。使い方は同じで、これらのプロパティはレコードセットの移動範囲内で境界を超えていないかどうかを調べる時に利用します。

簡単に特徴を述べると以下のようなものがあります。

  • カレントレコードが先頭のレコードよりさらに前に移動した時は、BOFプロパティはTrueを返します。それ以外はすべてFalseを返します。
  • カレントレコードが末尾のレコードよりさらに後に移動した時は、EOFプロパティはTrueを返します。それ以外はすべてFalseを返します。
  • 1つ以上のレコードを格納しているRecordsetオブジェクトを開くと、先頭のレコードがカレントレコードとなり、BOF及びEOFプロパティはどちらもFalseの状態になります。
  • 戻り値としては、どちらのプロパティもBoolean（ブール型）の値を返します。

Recordetオブジェクトの先頭のレコードから末尾までのすべてのレコードを対象として処理をするには、一般的にDo Until～Loopステートメントを利用して、次のように記述します。

	Do Until rs.EOF
		If rs!金額 > 10000 Then
			rs.Update "金額", Fix(rs!金額 * 1.05)
		End If
		strData = strData & vbNewLine & _
			rs!売上日 & ":" & rs!商品名 & ":" & rs!金額
		rs.MoveNext
	Loop

レコードセットを開いた直後は、先頭レコードがカレントレコードとなるので、末尾まで処理を繰り返すために、Do Until rs.EOFと記述します。繰り返す処理の後に、次のレコードに移動するためにrs.MoveNextメソッドを記述します。


また、EOFをBOFプロパティに置き換えることができます。その場合、まず末尾のレコードに移動して、先頭レコードまで繰り返す処理を行わせます。カレントレコードは末尾なので、次はその前のレコードに移動させることになるのでMovePreviousメソッドを使用します。次のように記述します。

	rs.MoveLast
	Do Until rs.EOF
		If rs!金額 > 10000 Then
			rs.Update "金額", Fix(rs!金額 * 1.05)
		End If
		strData = strData & vbNewLine & _
			rs!売上日 & ":" & rs!商品名 & ":" & rs!金額
		rs.MovePrevious
	Loop

末尾の方から処理を行いたい場合に、このような記述をします。

Bookmarkプロパティ

Recordset内のあるレコードに移動してから、元のレコードに戻りたい時にBookmarkプロパティを使用できます。RecordsetオブジェクトのBookmarkプロパティは、レコード移動するたびに変わります。戻りたいレコードのBookmarkプロパティをバリアント型の変数に格納して、レコードに戻るときに、Bookmarkプロパティに変数の値を代入します。

【書式】　Recordsetオブジェクト.Bookmark = 値

《 記述例 》

テーブル「T_売上」のレコードを金額を基準に降順で表示し、一番売上金額の高いレコードを表示するには以下のように記述します。

	Sub ブックマーク()
		Dim cn As ADODB.Connection
		Dim rs As ADODB.Recordset
		Dim bm As Variant
		Dim mySQL As String
		Dim strData As String

		Set cn = CurrentProject.Connection
		Set rs = New ADODB.Recordset

		mySQL = "SELECT * FROM T_売上 ORDER BY 金額 DESC;"

		rs.Open mySQL, cn, adOpenKeyset, adLockOptimistic
		bm = rs.Bookmark

		Do Until rs.EOF
			strDATA = strDATA & vbNewLine & rs!売上日 & ":" & rs!商品名 & ":\"
			strDATA = strDATA & Format(rs!金額, "#,##0") & ":" & rs!区分
			rs.MoveNext
		Loop

		rs.MovePrevious
		rs.Bookmark = bm
		MsgBox strDATA & vbNewLine & "一番の売上金額は" & rs!売上日 & "の" & _
			Format(rs!金額, "#,##0") & "円です。"

		rs.Close: Set rs = Nothing
		cn.Close: Set cn = Nothing
	End Sub

bm = rs.Bookmarkで、先頭レコード（カレントレコード）を登録します。rs.MovePreviousの記述は、EOFプロパティがFalseに変化しているので、カレントレコードを有効にしています。そして、rs.Bookmark = bmの記述で登録している値（先頭レコード）をRecordsetオブジェクトに代入しています。

動作の結果は次のようになります。