【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法

photo credit: 20130930 – Chris H visits – spinning shotglass – Chris’s Spin – 225849 (20s) (48khz mono snd).mp4 via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。

前回が以下の記事ですか、何回かに分けてAccessデータベースからいくつかの方法でデータを抽出する方法についてお伝えしました。

【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はSQL文にJOINを使うことで複数のテーブルを組み合わせてデータを取り出す方法についてお伝えします。

少し方向性を変えまして、エクセルVBAからデータベースにレコードを追加する方法についてお伝えしたいと思います。

今回はその最もシンプルな方法として、AddNewメソッドを使う方法についてお伝えします。

サンプルとするテーブルについて
Accessデータベースにレコードを追加する
レコード追加の際のADODBレコードセットのオープン
1. Openメソッドの第一パラメータはテーブル名またはSQL文
2. カーソルタイプ、ロックタイプのパラメータ
AddNew,Upadateメソッドでレコードを追加する
1. 代入する値の指定の方法
2. 値を代入しないフィールドはどうなるか
実行結果
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

サンプルとするテーブルについて

まずサンプルとして、このようなテーブルをAccessデータベース「test2.accdb」内に用意しました。

テーブル：都道府県
フィールド：ID｜登録日｜都道府県｜推計人口

フィールド「ID」を主キーかつオートナンバー型にしていて、「登録日」は日付型、「都道府県」はテキスト型、「推計人口」は数値型のフィールドです。

このテーブルに一行のレコードを追加するプログラムを作っていきたいと思います。

Accessデータベースにレコードを追加する

上記Accessファイル「test2.accdb」と同じフォルダに、Excelマクロ有効ブックファイルを作成し、このプログラムを作成してみましょう。

Sub addRecord()

Dim strFileName As String
strFileName = "test2.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

With adoRs
    .Open "都道府県", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く

    .AddNew
        !登録日 = #2016/09/12#
        !都道府県 = "岩手県"
        !推計人口 = 1284384
    .Update

    .Close 'レコードセットのクローズ

End With

adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

これまでのシリーズでお伝えしている部分のおさらいとしますと、6行目～11行目は

ADODBコネクションオブジェクトを生成して開く
ADODBレコードセットオブジェクトを生成する

という処理、また飛んで26行目～29行目については

ADODBレコードセットオブジェクトを開放する
ADODBコネクションオブジェクトを閉じて開放する

という処理です。ここはデータベースからデータを抽出する場合と、データベースにレコードを追加する場合とほぼ一緒の作りでOKです。

残りの13行目～24行目がレコードの追加に関する部分となりますので、以下で説明をしていきます。

レコード追加の際のADODBレコードセットのオープン

レコードを追加する場合もADODBレコードセットオブジェクトを使うため、Openメソッドでオープンをします。

データベースからデータを抽出する場合は

ADODBレコードセットオブジェクト.Open SQL文, ADODBコネクションオブジェクト

と書きましたね。

しかし、レコードの書き込みをする場合は、少し記述方法が異なります。

ADODBレコードセットオブジェクト.Open テーブル名, ADODBコネクションオブジェクト, adOpenKeyset, adLockOptimistic

と書きます。

ちょっと長いですが…

この方法でオープンすることにより、ADODBレコードセットオブジェクトを通して、テーブルにレコード追加の処理をすることができるようになります。

Openメソッドの第一パラメータはテーブル名またはSQL文

まず、テーブル名ですが、データの抽出のときはここにはSQL文が入っていましたね。

しかしここの部分には、実はSQLではなくてテーブル名をそのまま記述することができます。

ですから例えば

Dim strSQL As String
strSQL = "SELECT * FROM 都道府県"
adoRs.Open strSQL, adoCn

というように抽出したデータと

adoRs.Open "都道府県", adoCn

と抽出したデータは結果が同様になります。

このように、ADODBレコードセットオブジェクトのOpenメソッドの一つ目のパラメータとしては、テーブル名とSQL文とどちらでも良いということを覚えておいて頂ければと思います。

カーソルタイプ、ロックタイプのパラメータ

データの抽出のときには必要なかったパラメータであるadOpenKeyset,adLockOptimisticですが、これはそれぞれカーソルタイプ、ロックタイプを指定するパラメータとなります。

データ抽出の際には省略可能でしたが、既定値ではデータの追加が許可されませんので、データの追加の際にはこれらのパラメータを指定するようにしましょう。

AddNew,Upadateメソッドでレコードを追加する

レコードの追加をする場合は

ADODBレコードセットオブジェクト.AddNew
ADODBレコードセットオブジェクト!フィールド名1=値1
ADODBレコードセットオブジェクト!フィールド名2=値2
…
ADODBレコードセットオブジェクト.Update

と記述します。

まず、AddNewメソッドで新規レコードを追加します。その時点で、カレントレコード（現在対象となるレコード）が、新規追加されたレコードに移動します。

次にそのカレントレコードの各フィールドに値を代入します。フィールドはADODBレコードセットオブジェクトに続き「!」（エクスクラメーションマーク）、そしてフィールド名で表現することができます。

最後にUpdateメソッドでカレントレコードの変更が保存されます。

ちなみに、ADODBレコードセットオブジェクトについてWithを使用すると、プログラムがスッキリしますね。

代入する値の指定の方法

また代入する値ですが

数値型：そのまま
テキスト型：ダブルクォーテーション（”）で囲む
日付型：シャープ記号（#）で囲む

というルールがあります。特に日付型はならではのルールなので注意ください。

値を代入しないフィールドはどうなるか

またテーブルにはフィールドが存在するのにAddNewメソッドとUpdateメソッドの間に指定しなかったフィールドはどうなるのか、ということについては

フィールドがオートナンバー型：自動で採番される
フィールドの入力規則が必須ではない：Null値が代入される
フィールドの入力規則が必須：エラーが発生する

となります。

実行結果

プログラムの実行結果です。

このように一行だけではありますが、無事にレコードが追加されていますね。

まとめ

以上、エクセルVBAからAccessデータベースのテーブルにレコードを追加する方法についてお伝えしました。

ADODBレコードセットオブジェクトに対するAddNewメソッド、Updateメソッド、そして各フィールドへの代入の方法について覚えて頂ければよいと思います。

ところで、SQLにちょっと詳しい方は「SQLのINSERT文を使ってもいいのでは？」と思われるかも知れません。

もちろん、その方法でも目的を達成することができるのですが、エクセルVBAからレコードを追加する場合、今回のADODBレコードセットオブジェクトに対するAddNewメソッド、Updateメソッドの方法のほうが処理が速いのです。

全部SQLでできたら、独自のメソッドとか覚えなくて楽は楽なんですけどね。

さて、次回ですが、複数のレコードを追加する方法についてお伝えできればと思います。

エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について

エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。今回はエクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度についてお伝えします。

どうぞお楽しみに！

連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。