ACCESS VBA ADOでレコードを追加する方法

ACCESSのデータはテーブルに格納されるレコードが元になります。

レコードは入力フォームなどから手入力したり、Excelやcsvからインポートすることができます。

一方で、自動処理の中で自動でレコードを追加したい場合もあるでしょう。

ACCESSの基本機能だけでは自動でのレコード追加ができません。

そこで利用するのが、ADOです。

今回は、VBAでADOを使ってレコードを追加する方法をご紹介します。

---

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

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

ADOとは

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

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

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

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

ADOでのレコード追加

ADOでレコードを追加する場合は、AddNewメソッドを利用します。

AddNewメソッドは以下の構文で使用します。

レコードセット名.AddNew

　レコードセット名!フィールド名1 = 値1
　レコードセット名!フィールド名2 = 値2
　レコードセット名!フィールド名3 = 値3

レコードセット名.Update

最初にAddNewで追加することを宣言した後に、それぞれのフィールドと値を設定します。

最後に、Updateを行うことでレコードが追加されます。

Updateを書き忘れやすいので注意してください。

サンプルテーブル

では、実際にADOでレコードを追加する処理を見てみましょう。

今回はサンプルテーブルとして年月と年度を格納したテーブルを用意しました。

年度は3月締めになっているので、4月より次の年度に変わります。

このテーブルは売上の集計を行う際に集計単位として利用することを想定して作っています。
そのため、新しい月になったときには手動で追加するのではなく、現在の日付を見て自動的にレコードを追加したいと考えています。

そこで、ADOを使って新しい月のレコードを自動追加する処理を作成しましょう。

ADOの使用準備

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

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

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

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

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

VBAプログラミング

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

Public Sub nengetsu_koushin()

Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim max_nengetsu As String
Dim max_nendo As String

max_nengetsu = DMax("年月", "TRN_年月")

'今日の年月とTRN_年月の最新年月が同じかチェック
If Format(Date, "yyyy/mm") = max_nengetsu Then

    '同じなら終了
    Exit Sub

'違っていた場合
Else

    '変数にADOオブジェクトを代入
    Set cnn = CurrentProject.Connection
    Set rst1 = New ADODB.Recordset
    rst1.CursorLocation = adUseClient
    
    'レコードセットを取得
    rst1.Open "TRN_年月", cnn, adOpenKeyset, adLockOptimistic
          
    Do Until max_nengetsu = Format(Date, "yyyy/mm")

        Select Case Right(max_nengetsu, 2)
        
            Case Is <= "02"
            
                Debug.Print "case1"
                max_nengetsu = Left(max_nengetsu, 4) & "/0" & LTrim(Str(Val(Right(max_nengetsu, 2)) + 1))
                max_nendo = LTrim(Str(Val(Left(max_nengetsu, 4) - 1)))

            Case "09" To "11"
            
                Debug.Print "case2"
                max_nengetsu = Left(max_nengetsu, 4) & "/" & LTrim(Str(Val(Right(max_nengetsu, 2)) + 1))
                max_nendo = Left(max_nengetsu, 4)

            Case "12"
            
                Debug.Print "case3"
                max_nengetsu = LTrim(Str(Val(Left(max_nengetsu, 4)) + 1)) & "/01"
                max_nendo = LTrim(Str(Val(Left(max_nengetsu, 4) - 1)))
            
            Case Else
            
                Debug.Print "case4"
                max_nengetsu = Left(max_nengetsu, 4) & "/0" & LTrim(Str(Val(Right(max_nengetsu, 2)) + 1))
                max_nendo = Left(max_nengetsu, 4)
        
        End Select
               
        rst1.AddNew
                
            rst1!年月 = max_nengetsu
            rst1!年度 = max_nendo
        
        rst1.Update
                    
    Loop
    
    '終了処理
    rst1.Close: Set rst1 = Nothing
    cnn.Close: Set cnn = Nothing

End If

End Sub

処理概要

今回のコードでは、変数「max_nengetsu」にテーブル「TRN_年月」のレコードの中で最新の年月を格納し、それが操作時点の年月と同じかどうかをチェックします。

その上で、この2つが異なる、つまりTRN_年月に最新の年月のレコードが無い場合には、max_nengetsuに+1した年月のレコードをTRN_年月に作成します。

この処理を、max_nengetsuと操作時点の年月が同じになるまでループで繰り返しています。

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

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	ロックタイプを指定しない

追加するレコードに設定する値の取得

この後は、Do Until　～　Loopを使って、max_nengetsuと操作時点の年月が同じになるまでTRN_年月へレコードを1つずつ追加していきます。

Select Case Right(max_nengetsu, 2)

で条件分岐をさせていますが、年月の値の右2桁、つまり月を示す数字によって4パターンに分岐させ、max_nengetsuとmax_nendoに新しい値（追加するレコードの値）を入れていきます。
今回は年度を3月締めで計算しています。

現在のmax_nengetsuの月	新しいmax_nengetsu	新しいmax_nendo
1、2月	max_nengetsuの年にテキストの0と月 + 1（月が1桁のため0を付与）	max_nengetsuの年 – 1
9月～11月	max_nengetsuの年と月 + 1	max_nengetsuの年
12月	max_nengetsuの年に + 1と月は01固定	max_nengetsuの年 – 1
その他	max_nengetsuの年にテキストの0と月 + 1（月が1桁のため0を付与）	max_nengetsuの年

レコードの追加

max_nengetsuとmax_nendoに条件に応じた値が入ったところで、テーブルへのレコードの追加処理を行います。

        rst1.AddNew
                
            rst1!年月 = max_nengetsu
            rst1!年度 = max_nendo
        
        rst1.Update

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

rst1.AddNewではじめ、rst1!〇〇（〇〇はテーブルのフィールド名） = 値、を繰り返し、rst1.Updateで確定させます。
最後のUpdateを記載し忘れるとレコードが追加されませんのでご注意ください。

以上、ADOでレコードを追加する方法をご紹介しました。

---

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

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

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

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