'接続済みのデータベースのテーブルへのレコードセットの接続 zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3 ’レコードの新規作成 zzDB_Rec.AddNew ’新規作成したレコードへの代入 zzDB_Rec!ID = ActiveCell ’レコードのデータベースへの更新 zzDB_Rec.Update ’レコードセットの接続を解除 zzDB_Rec.Close
データベースへのレコードの新規追加にはADOオブジェクトの「AddNew」メソッドを使用します
このメソッドを実行しただけでは既定値が入力されたレコードが作成されるだけになります
その追加したレコードに代入するにはADOオブジェクトのインデックス番号か、例コードのように列名を指定して代入します
代入後に「Update」メソッドを実行することでデータベースへの登録更新が完了します
操作後はADOオブジェクトの接続を解除します
レコードの新規追加といっても、実用で考えるとこの一連の手順が最低限必要になります
また、レコードの新規追加にはSQLのINSERT文も利用可能ですが、圧倒的にこちらのメソッドの方が高速なため、基本的にはレコードの追加はこちらのコードを利用します
メソッドの書式
引数（太字は必須引数）
AddNew FieldList, Values
このメソッドには引数が2つあり、どちらも省略可能です
この引数はそれぞれが対応しています
「FieldList」は列名を指定して、「Values」はその代入値の指定です
この指定は列名を文字列として指定して値を入力させます
この引数のイメージはSQLでのINSERT文の引数と同じイメージになります
また、この引数は配列を指定する必要があるためArray関数を使用して配列として指定する必要があります
zzDB_Rec.AddNew Array("ID", "日付"), Array(ActiveCell.Value, ActiveCell.Offset(0, 1).Value)
この様に引数の2つをどちらも指定して、かつ配列として指定する必要があります
こうすることで、例コードの様に1列ずつ代入せずに一括代入が出来ます
しかし、おおよそ一括代入は処理が高速になるはずなのですがこのメソッドはその通りでは無いようです
ほんの数回程度の検証しかしていませんが、10万件2列のデータの取得を行ったところメソッドの引数を使用した場合は1.4秒前後となりました
対して、各列ごとの取得では1.1秒前後となり、0.3秒もの差がつきました
CPU性能等に非常に影響があるので、数字に意味はありませんがコードの可読性含めて考慮するとほぼ利用する意味がありません
どの列に何を代入しているかは各列入力の方が分かりやすいです
実際の使用例
シートのテーブルに入力されたデータをデータベースに登録を行います
上記の引数説明にあるように、メソッドの引数を指定する方法は利用しません
コードの動きに関しても、データベースへの登録作業のため逐一の動きが確認できませんので結果のみを画像表示します
この画像はExcelに入力されたテーブル内容のデータです
今回のコードはこの画像のデータをデータベースに登録を行います
自分の環境にはAccessがありませんので、データの確認としてExcelのナビゲーター画面を利用します
この画像の右範囲を確認してもらうと分かるように、まだデータベースにデータは何も登録されていませんので、空のテーブルであることが確認できます
先に結果の画像を確認してみます
先ほどは空のテーブルであった部分にデータが入力されています
Nullというのはデータベースでデータが無い場合の値です
「””」は文字列の空白を意味するので、Null=””は成り立ちません
Excelのセルの空白はデータベースではNullが代入されます
これを防ぎたい場合は、空白の判定処理を入れる必要があります
Nullが許可されていない列の場合は実行時エラーが発生します
コード解説
Sub 処理テスト()
Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")
'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3
Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop
zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing
End Sub
上の処理を実際におこなったコードです
Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")
ここではデータベースへの接続のConnectionオブジェクトとRecordsetオブジェクトの作成を行っています
レコード操作を行う場合はRecordsetオブジェクトを利用します
'データベースへの接続開始 zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"
データベースへの接続を開始します
ここではAccdb形式のデータベースへの接続を行っています
Providerに関してはほぼ固定なので文字列をコピペしてもらって大丈夫です
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3
ここでRecordsetオブジェクトを接続しています
引数の1つ目はテーブル名を文字列で指定します
2つ目の引数はデータベースへの接続を指定するので、前の手順で接続したConnectionオブジェクトを指定します
3つ目と4つ目はレコードを操作する際の方法の設定になります
詳細は別の記事にて行います
ここでは、レコードを前方移動で共有ロックをかけています
ロックに関しては「3」を「2」に変えても良いです
なお、この数値は参照設定を行えば定数が利用できます
参照設定を行っていないとエラーとなるので、数値を直接指定すると安定感があります
Do Until ActiveCell = "" zzDB_Rec.AddNew zzDB_Rec!ID = ActiveCell zzDB_Rec!日付 = ActiveCell.Offset(0, 1) zzDB_Rec!コード = ActiveCell.Offset(0, 2) zzDB_Rec!金額 = ActiveCell.Offset(0, 3) ActiveCell.Offset(1, 0).Select zzDB_Rec.Update Loop
ここでループ処理により空白セルになるまで行のデータを登録していっています
新規追加して、データを各列ごとに代入させています
zzDB_Rec!ID = ActiveCell
各列に代入する場合はRecordsetオブジェクトに「!」で列名を接続して指定することで列を限定できます
AddNewメソッドの引数だと1行に全てを一気に記載する必要がありますが、例の様に各列を指定できるので、こちらのほうが見やすいです
そのうえ速いなら選択の余地がないかもしれませんね
zzDB_Rec.Update
「Update」メソッドは更新した内容をデータベースに確定させるメソッドです
この時点でデータベースが更新されます
なお、AddNewメソッドはUpdateメソッドが実行されていないレコードがあった場合に、AddNewメソッドが実行されると自動的に更新確定を行います
zzDB_Rec.Close Set zzDB_Rec = Nothing zzDB_Con.Close Set zzDB_Con = Nothing
最後にRecordsetオブジェクトの接続解除、データベースへの接続解除を行います
これはお決まりの流れです
以上の様にレコードは登録処理を行います
AddNewメソッドは非常に高速です
さらに、このメソッドを最速の環境にする方法としてデータベースを接続せずにテーブルをADOオブジェクト上に作成して、レコード登録が最も早いです
およそ、この記事での例コードの倍ほどの速さを発揮します
すこしテーブル作成がやっかいなのがネックです