Access VBAのRecordsetについてお探しですね。

広告

【VBA】Recordsetを使いこなそう！Accessのデータを自由に操作する方法

Accessで業務システムを作っていると、普通のクエリだけでは対応できない複雑な処理に出会うことがありますよね。

「条件によってデータを書き換えたい」「他のテーブルを見ながら計算して登録したい」といった場面では、VBA（Visual Basic for Applications）を使ったプログラミングが必要になってきます。

そんな時に最強の味方となるのが「Recordset（レコードセット）」です。

Recordsetを使えるようになると、Accessのデータをメモリ上で自由に扱えるようになり、追加・更新・削除といった操作を思い通りにコントロールできるようになります。

この記事では、将来性の高い「ADO（ActiveX Data Objects）」を使ったRecordsetの操作方法を、実務で役立つテクニックを交えながらわかりやすく解説していきます。

Recordsetって何？ADOを使う準備をしよう

Recordsetとは、データベースのテーブルから取り出したデータを、プログラム上で扱えるようにメモリに読み込んだ「仮想のテーブル」のことです。

Access VBAでデータを操作する方法には「DAO（Data Access Objects）」と「ADO（ActiveX Data Objects）」という2つの方法がありますが、今回はより汎用性が高く、SQL Serverなど外部のデータベースとも連携しやすいADOを中心に説明します。

ADOを使うには、まずVBAの編集画面（VBE）でライブラリの参照設定をする必要があります。

「ツール」メニューから「参照設定」を開いて、「Microsoft ActiveX Data Objects x.x Library」（x.xはバージョン番号で、6.1などの新しいものを選びましょう）にチェックを入れてください。

これでADO専用の機能が使えるようになります。

参照設定ができたら、次はコード内でデータベースへの接続とRecordsetの準備をします。

ADOでは、データベースへの接続を管理する「Connectionオブジェクト」と、データを入れる「Recordsetオブジェクト」を組み合わせて使います。

今開いているAccessファイルのデータを操作する場合は、`CurrentProject.Connection`を使うと簡単に接続できます。

Recordsetを開くときは、`Open`メソッドを使って、対象のテーブル名やSQL文、接続情報、カーソルタイプ、ロックタイプを指定します。

特にロックタイプは重要です。

データを追加したり更新したりする場合は、読み取り専用ではなく書き込み可能な設定（`adLockOptimistic`など）を選ぶ必要があります。

こうした準備をしっかりやっておくことが、エラーの出ない安定したプログラムを作る第一歩になります。

新しいデータを追加するAddNewメソッド

テーブルに新しいレコードを追加するには、ADOのRecordsetが持っている`AddNew`メソッドを使います。

このメソッドは、Recordset上に新しい空の行を作って、データ入力を受け付ける状態にしてくれます。

基本的な流れはこうです。

まず`Recordset.Open`でテーブルを開いて、`Recordset.AddNew`を呼び出します。

次にフィールド名（列名）を指定して値を入れて、最後に`Recordset.Update`メソッドを実行すると、変更内容が実際のデータベースに保存されます。

この「Update」を忘れてしまうとデータが保存されずに消えてしまうので、必ずセットで書くようにしてください。

具体的な書き方としては、フィールドに値を入れるときは「!（エクスクラメーションマーク）」を使った書き方が一般的です。

たとえば、`rs!顧客名 = “株式会社A”`のように、わかりやすく書けます。

大量のデータを一気にインポートするような処理を作る場合、Recordsetを使ったループ処理がとても便利です。

フォームの入力値や外部ファイルから読み取った値を次々とセットして、連続してレコードを作成できます。

このとき、オートナンバー型の主キーフィールドには自動的に番号が振られるので、プログラム側で値を指定する必要はありません。

もし入力データにミスがないかチェックしたい場合は、Updateメソッドを呼び出す前にIf文などで検証処理を書いておけば、おかしなデータが登録されるのを防げます。

既存のデータを更新する方法

既存のデータを更新する場合、まずは更新したいレコードを見つける必要があります。

Recordsetを開くときに、テーブル全体を読み込むのではなく、SQLの`SELECT * FROM テーブル名 WHERE 条件`という書き方で、あらかじめ更新したいレコードだけに絞り込んでおく方法が効率的です。

もし全件を読み込んだ状態で特定のレコードを探す場合は、`Find`メソッドを使って条件に合うレコードへカーソル（今いる位置）を移動させます。

目的のレコードが見つかったら、フィールドに新しい値を入れて、追加のときと同じように`Update`メソッドを実行すれば変更が保存されます。

複数のレコードをまとめて更新したいときは、`Do Until rs.EOF`（レコードセットの最後になるまで繰り返す）というループを使います。

ループの中で1行ずつ値をチェックして、必要なら書き換えて、`MoveNext`メソッドで次のレコードへ移動する、という処理を繰り返します。

ここで注意したいのが、`MoveNext`を書き忘れると無限ループになって、Accessが固まってしまうことです。

また、更新処理で大切な考え方に「排他制御」があります。

Recordsetを開くときの`LockType`パラメータで`adLockOptimistic`（楽観的ロック）を指定しておけば、更新する直前まで他の人のアクセスを邪魔せず、書き込みの瞬間だけロックをかけることができます。

これなら複数の人が使うシステムでもトラブルが起きにくくなります。

データを安全に削除する方法とトランザクション

不要になったデータを削除するには、Recordsetの`Delete`メソッドを使います。

このメソッドを実行すると、今カーソルがある行のレコードがすぐに削除されます。

削除は元に戻せないので、実行前には必ずメッセージボックスを表示してユーザーに確認を取るなど、慎重な設計が必要です。

また、レコードを削除した直後は、カーソルが「削除されたレコード」の位置に残ったままになることがあります。

そのまま次の操作をしようとするとエラーになる場合があるので、削除後はすぐに`MoveNext`などで有効なレコードへ移動する処理を入れておくのがコツです。

データ操作で一番気をつけたいのが、処理の途中でエラーが起きたときのデータの整合性です。

たとえば、「売上データを追加して、同時に在庫データを減らす」という処理があったとします。

もし売上データの追加には成功したけど、在庫データの更新中にエラーが起きて処理が止まってしまうと、データの辻褄が合わなくなってしまいますよね。

こういう事態を防ぐために、「トランザクション処理」を実装します。

`Connection.BeginTrans`でトランザクションを開始して、すべての処理が成功したら`Connection.CommitTrans`で確定、途中でエラーが起きたら`Connection.RollbackTrans`で全ての変更を取り消すように書きます。

これなら予期しないエラーが起きても、データベースを処理前の正常な状態に戻すことができて、業務システムの信頼性がぐっと高まります。

広告