ACCESS VBA レコードを一括削除する方法(VBAコード公開)

ACCESSのテーブル操作では、毎回テーブルのレコードをすべて空にして、新しいレコードを追加しなおす、という処理を作ることがあります。

テーブルのレコードを一括削除するのは簡単に思えますが、VBAの通常の関数ではレコードの更新ができません。
テーブルを操作する際はADO(ActiveX Data Object)を使用する必要があります。

今回は、ADOを使ってVBAからテーブルのレコードを一括削除する方法をご紹介します。


こんにちは。
はこにわガジェット (@hakoniwagadget) です。

ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは基本機能だけでも十分便利ですが、VBAを使うことで格段に使いやすいデータベースを作成可能です。
この記事ではACCESSでのVBAの使い方をご紹介していきます。

VBAでレコードを削除する方法

まず、VBAでテーブルのレコードを削除する方法をまとめましょう。

基本的にVBAは、フォームを開けたり、クエリを実行したり、フィルターをかけたりはできますが、テーブルのレコード自体を直接操作することができません。

そのため、VBAからレコードを削除する場合は、以下の2つの方法のどちらかを選択することになります。

1.削除クエリを作成しておき、VBAで削除クエリを実行する

2.ADO(ActiveX Data Object)を使用してVBAからレコードを操作する

このうち、簡単なのは1.の方です。
それぞれ、詳しく見ていきましょう。

VBAで削除クエリを実行する

VBAでクエリを実行する際は、「DoCmd.OpenQuery」メソッドを使用します。

削除クエリの名称が「削除クエリ」だった場合、クエリの名称を”で囲って

DoCmd.OpenQuery “削除クエリ”

で実行できます。

なお、この際一つ注意点があります。

削除をクエリを含めたアクションクエリ(テーブルのデータを変更するクエリ)は、実行前に確認メッセージが表示されます。

例えば削除クエリならこんな感じですね。

手動でアクションクエリを実行した時であればメッセージに違和感はないのですが、VBAで自動処理をさせている中でこうしたメッセージが出ると、ユーザーはレコード削除を意図していない場合もあるため、「?」と思ってしまいます。

そのため、VBAでアクションクエリを実行する際は、こうしたアラートメッセージが表示されないようにするのが一般的です。

DoCmd.SetWarningsメソッドを使って下記のように記述すれば、一時的にアラートメッセージが出ないようにできます。


'アラートメッセージを停止
DoCmd.SetWarnings False

'削除クエリを実行
DoCmd.OpenQuery "削除クエリ"

'アラートメッセージを再開
DoCmd.SetWarnings True

ADOを使用してレコードを削除する

なぜADOが必要なのか

もう一つの方法として、ADO(ActiveX Data Object)を使用してレコードを削除する方法があります。

先に説明した削除クエリの方法が簡単なので、これで十分なのではないか、と思われるかもしれません。
しかし、削除クエリの方法には一つの欠点があります。

それは、事前に削除クエリを作っておかなければいけない、ということです。
もちろん、削除クエリを作成すること自体は簡単ですし、それほど問題ではありません。

問題なのは、削除クエリで削除するレコードの抽出条件を、事前に決めておくことができないケースがあるということです。

例えば、フォームでのユーザーの入力値に応じてレコードを抽出して削除するケースや、VBA処理中に作成した一時テーブルのレコードを削除する場合などです。

ACCESSをある程度使いこなし、VBAでの自動処理を進めてくるといつかこうしたケースに遭遇します。

ここで便利なのがADOを使ったレコード削除なのです。

ADOとは

ADO(ActiveX Data Objects)とはマイクロソフトが提供する、データベースを操作するための様々オブジェクトの集合体です。
ADOを使うことでデータベースの操作が可能です。

ACCESSはもともとクエリ等のデータベースを操作する独自オブジェクトを用意していますが、ADOを使うことで条件分岐やループを含めたより柔軟なデータベースの追加、更新、削除が可能です。

ADOとよく似たものに、DAO(Data Access Object)があります。
ACCESSのデータベース接続に特化している点が特長で、ADO登場以前から使われています。

ただ、今後VBAプログラミングを行うならADOを使っておくほうが良いでしょう。
ADOとDAOはプログラミングの方法が若干異なりますので、混在すると複雑になります。

ADOの使用準備

ADOを使用するためにはACCESS側で、ADOの利用に必要なライブラリファイルを追加する必要があります。
といっても、操作は簡単です。

ACCESSを起動させたら、ALT + F11キーでVBE(Visual Basic Editor)を表示します。

VBEの画面で、ツールから参照設定を選択します。

参照設定の画面が表示されますので、Microsoft ActiveX Data Objects X.X Libraryを選択して、OKを押します。
X.Xの部分はバージョンです。できるだけ数字の大きいもの(新しいもの)を選択してください。

これで、事前準備は完了です。

ADOを使ったレコード削除処理

それではVBAで年月を追加するプログラムを書いていきましょう。
今回は以下のコードを作成しました。

TRN_売上というテーブルから、売上日が


Public Sub sakujo()

Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset

'変数にADOオブジェクトを代入
Set cnn = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
    
'レコードセットを取得
rst1.Open "TRN_売上サンプル", cnn, adOpenKeyset, adLockOptimistic

'売上日で抽出
rst1.Filter = "売上日 = " & #2/3/2020#

Do Until rst1.EOF
    
    rst1.Delete
    rst1.MoveNext
    
Loop
    
'終了処理
rst1.Close: Set rst1 = Nothing
cnn.Close: Set cnn = Nothing

End Sub

処理概要

今回のコードでは、 テーブル「TRN_売上サンプル」から売上日が2020年2月3日のレコードを抽出して、該当のレコードを削除しています。

ADOでは削除処理は1レコードずつになるので、削除処理を、ループで繰り返しています。

以下、細かく解説していきます。

ADOの基本構文

ADOを利用する場合は、ある程度決まった記述があります。
最初に、


Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset

で、ADO用のオブジェクト変数を定義します。
そのうえで、


    Set cnn = CurrentProject.Connection
    Set rst1 = New ADODB.Recordset
    rst1.CursorLocation = adUseClient

で、ADOのオブジェクトに接続します。
cnnがプロジェクト全体、rst1がその中のレコードセット(テーブル)の一つを指すと考えてください。

この部分と、最後の終了処理である、


    rst1.Close: Set rst1 = Nothing
    cnn.Close: Set cnn = Nothing

は、毎回使用しますので、ADOを使用する際はまずはこの最初と最後を記述して、後から中身を埋めていくとよいでしょう。

レコードセットの取得

ADOのオブジェクトに接続したら、次はレコードセットを取得します。
定義したrst1にテーブルを代入するのです。


rst1.Open "TRN_売上サンプル", cnn, adOpenKeyset, adLockOptimistic

が該当部分になります。

今回はrst1というレコードセットオブジェクトに、テーブル「TRN_売上サンプル」を代入しました。

レコードセットの取得の際のパラメータは、以下の通りです。
基本は、adOpenKeyset, adLockOptimisticにしておけば問題はないです。

CursorType

adOpenForwardOnly前方スクロールカーソル。前方スクロールのみだが高速。検索を一度のみ実施する場合などに利用。
adOpenKeysetキーセットカーソル。他のユーザーによる追加・削除は確認できない。
adOpenDynamic動的カーソル。レコードをすべての方向に移動することができる。他のユーザーによる追加・削除が確認できる。
adOpenStatic静的カーソル。レコードをすべての方向に移動することができる。他のユーザーによる追加・削除は確認できない。

LockType

adLockReadOnly読み取り専用
adLockPessimisticレコード単位の排他的ロック
adLockOptimisticレコード単位の共有的ロック
adLockBatchOptimistic共有的バッチ更新
adLockUnspecifiedロックタイプを指定しない

該当レコードの抽出

今回は 2020年2月3日のレコードを削除したいので、Filterプロパティを使用してレコードを抽出します。


rst1.Filter = "売上日 = " & #2/3/2020#

日付を指定しているので、「”」ではなく「#」で囲うのを間違えないようにしてください。

ループでの削除処理

この後は、Do Until ~ Loopを使って、TRN_売上サンプルの最終レコードまで1レコードずつ削除していきます。


Do Until rst1.EOF
    
    rst1.Delete
    rst1.MoveNext
    
Loop

ADOを使ってレコードを削除する際は、上記の記述方法になります。

rst1.Deleteで削除し、rst1.mMoveNextで次のレコードへ移動します。

以上、レコードを一括削除する方法をご紹介しました。


ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。

そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私(@hakoniwagadget)にお任せください。

ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。

サービス一覧

最後までお読みいただき、ありがとうございました。