レコードの新規追加(ADODB.Recordset)

AccessデータベースにExcelのデータをレコードとして新規追加するコード

'接続済みのデータベースのテーブルへのレコードセットの接続
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のテーブルデータ
Excelのテーブル内容

この画像は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オブジェクト上に作成して、レコード登録が最も早いです
およそ、この記事での例コードの倍ほどの速さを発揮します
すこしテーブル作成がやっかいなのがネックです

データベースにSQL文を実行する(ADODB.Connection)

データベースに対してSQL文を実行するExecuteメソッドについて

'テーブルを作成するSQL文を実行する
DBCone.Execute "CREATE TABLE テーブル(ID Long,日付 Date)"

データベースにSQL文を実行するには、Executeメソッドを使用します

このメソッドを使用する場合は、レコードの取得を行わないSQL文にする必要があります
UPDATE文やINSERT INTO文などになります

レコードを取得したり、関数の結果を取得したりする場合はADODB.Recordsetオブジェクトを利用します

引数にはSQL文を1行の文字列にして指定することで、その構文が実行されます
SQLでも構文の改行がありますが、VBAから実行する場合は改行は無効となりますので1行の構文を作成するようにしてください
なお、このメソッドの実行前にデータベースへの接続を行っておく必要がありますので注意してください
接続に関しては以下の記事を確認してください

ExcelVBAとしてはメソッドの実行を行うのみになりますが、引数に指定するSQL文は別の言語なので様々な指定を行うことが出来ます
今回の例コードではテーブルの作成を行っています
その際フィールドを2つ作成しています

実際、このコードをそのままコピペして使用することはほぼ出来ません
SQL文の内容が実際の使用場面と全く違うからです

ここではSQLの実行方法の解説なので例コードにあるSQL文についての詳しい解説は割愛させてもらいます

このメソッドの大きな利点は、データベースを丸ごと処理できることにあります
UPDATE文を使用すれば、データベース全体に編集を一括で行うことが出来ます
また、テーブルやビューの作成・削除などもこのメソッドから行います

ExcelVBAでデータベースを操作する場合は、テーブルの作成が必要です
テーブルの作成を行わずにレコード操作を行うことも出来ますが、それはまた別の話になってきます

ただ、問題があるとすればリアルタイムで処理の動きを確認できないところです
VBAのデバッグの様に、1行ずつ確認しながら作業を行えませんので、SQL文がどのような結果をもたらすかを最低限の範囲でイメージできていないと、データベース操作は難しく感じます

また、AccessSQLも有名で書籍もあふれているものではありません
ほかのSQLで使用できるものが使用できなかったりすることもありますので、実際に動きを確認しながら操作を進めるようにしてください

ちなみにINSERT INTO文はVBAから行うと非常に低速なので、ほぼ使うことはありません
ADODB.Recordsetオブジェクトを利用する方が比較対象にならない程に早いです

データベース取得用オブジェクトについて(ADODB.Recordset)

Accessデータベースからレコードデータを取得する際に代入させるオブジェクトの宣言と使用について

'操作用オブジェクトの作成
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

'レコード操作処理

Set zzDB_Rec = Nothing

データベースの操作にはSQLを使用する方法と、VBAのオブジェクト利用(以下、ADO)を使用する方法の2通りがあります
どちらの操作方法を使用するにしてもレコードを取得する場合はオブジェクトの作成が必要になりますので例コードを使用してください

データベース操作なので、基本的にADOで出来ることはSQLで可能です
なので操作することだけを考慮すればSQLが優位ですが、実際のVBAでのコードの分かりやすさと単体レコードでの処理速度はADOが優位です
なので、どちらが完全に優位というものではありませんので、状況に合わせて使用を切り替えるようにしてください

切り替える基準の分かりやすい点として

  • 単体のレコード操作を行う場合はADO
  • 複数のレコード操作を行う場合はSQL

と認識して使用を切り分けてください

例えば、ワークシートにあるデータをデータベースに登録する場合に一括で登録することは出来ませんので1レコードずつ登録を行います
この場合にはADOを利用してください

また、データベース上の特定の文字列を置き換えしたい(システム名を正式名称に置き換えなど)の場合には一括で操作を行えるSQLを利用します

結果的にレコードが1つだったとしてもデータベースからのSELECT文はSQLを利用する方が便利です、厳密にはデータベース全てのレコードを操作することになります

例コードはレコードを代入させるためのオブジェクトになります
つまりレコードを取得する必要のない処理であればこのオブジェクトは不要です
例えば、UPDATE文やDELETE文などデータベースのレコードを編集するだけで、そのレコードを返さないような処理の場合です
この場合は、レコードのオブジェクトは不要で、接続用オブジェクトにSQLを実行してもらう形になります

データベースにテーブルを作成する(ADODB.Connection)

作成したデータベースにテーブルを作成するコード

'テーブルの作成
zzDB_Con.Execute "CREATE TABLE テーブル名(ID Long,日付 Date,コード String(20),金額 Currency)"

接続しているデータベースにテーブルを新規作成するには、SQL文のCREATE TABLE文を使用します

この構文を実行することで、レコードを収めるためのテーブルが作成されます

このコードはデータベースへの接続が完了している必要があります
接続に関しては以下の記事を確認してください

テーブルとはExcelでいうシートのようなもので、これが無いとデータベースにレコードを登録することが出来ません

また、1度作成してしまえばそこにレコードを保存していくので処理中に頻繁に出てくるような構文ではありません

また接続しているデータベースにSQLを実行するにはExecuteメソッドを利用します
引数にSQL文を文字列で指定します

Accessの無いPCであれば実際にデータベースの中身を確認できないので、このコードを実行しても何が起きているのか分かりませんが、再度実行してみてください
実行時エラーが発生し、すでに存在していることが分かります
とりあえずはそれで確認は十分です

コード解説

zzDB_Con.Execute "CREATE TABLE テーブル名(ID Long,日付 Date,コード String(20),金額 Currency)"

CREATE TABLE文にはまず、テーブル名を指定します
ここでは「テーブル名」という名称が指定されています
テーブル名という名前のテーブルを作成する、という意味になります

次に半角カッコで挟む形で作成するフィールドの指定を行います
フィールドとはExcelでいう列のことになります

AccessデータベースはExcelとは違い列は作成しなければいけません
またExcelと大きく違うのがフィールドには入力できるデータの型を指定する必要があることです
Excelはどんなものでも入力できるセルがありますが、どんなものでも入るのは便利な反面、何が入力されているかをいちいち判定する必要があり、ユーザーの入力ミスも往々にして発生します
そういったことを回避するためにデータベースでは事前に入力できる制限を設けることが出来ます

制約というものになりますが、今回は型の紹介だけ行っておきます

(ID Long,日付 Date,コード String(20),金額 Currency)"

まず、IDという名称のフィールドを作成します
このフィールドの型はLong型なので整数型になります
実際AccessデータベースにLong型は存在しません
ですが、自動的に同意義の型に変換してくれているのであまり気にしなくても良いです
さすが同じOfficeソフトです

つまり、このフィールドは整数値のみが利用可能で文字列は登録できないことになります

次に日付というフィールド名でDate型(日付型)を作成しています
日付型なので時間等も含めて登録することが可能です

次にコードという名称で文字列型のフィールドを作成しています
フィールドを複数作成する場合は、フィールドごとに半角カンマで区切ります

これは無くても構わないのですが、(20)という文字が追記されています
これは文字数制限になります
これ以上の文字列を登録しても切り捨てられて、この文字数で登録されます

最後にCurrency型(金額型)の金額というフィールドが作成されます
金額型なのでお金のデータを登録する際に利用します
ここら辺の型の細かいところは、変数に関する記事を確認してください

ExcelVBAで使用できる変数の型はほぼ利用できます
Double型、Boolean型なども利用可能です

ここまで来たらようやくレコードの登録が可能になります
その際にテーブル名とフィールド名を使用しますので、Accessの無い方は分からなくならないようにしてください

ExcelVBAにおけるレコード操作について

ExcelVBAで利用するADOオブジェクトとSQLの併用について

ExcelVBAではデータベース操作の基本であるレコード操作がオブジェクトを利用することで編集することが出来ます
また、このオブジェクトを利用せずにSQL文を利用してレコード操作を行うことも出来ます

どちらも同じ結果を導くので、どっちゃでもええのですが操作内容によっては処理速度に大きな差が出るので、そこには注意してください

基本的にコードの実行回数が同じ回数を実行する場合は、SQLよりオブジェクトを利用した方が早いです

例えば、レコードの追加が明確に差があります
SQL文ではレコードの追加はINSERT文を使用します、これはフィールド名と入力内容を指定することでデータベースに新規レコードとして追加する構文です
それに対して、オブジェクトを利用する場合はAddnewメソッドを利用します
このメソッドはデータの入力は行いません、新規レコードを追加するだけです
このメソッドを実行した後に各フィールド名を指定して値を代入していきます

この2つの全く同じ結果を求められる処理では、後者のオブジェクト利用が圧倒的に早いです

そもそもの想定の違いかもしれませんが、SQLは基本的に細かいコードを連続して実行するものではなく、なるべく1回のコード実行で結果を出すものだからです
対してExcelVBAは、細かいコードを大量に実行します

なのでどちらが優位というものでもないのでしょうが、ExcelVBAにおいてのレコード操作という点ではExcelVBAのオブジェクト利用が優位です

そして、ここまでオブジェクトと記載してきましたが、実際にはADOオブジェクトというものを利用します
これと似たような操作のできるDAOオブジェクトもありますが古いオブジェクトとなりますのでADOオブジェクトを利用できるようになれば十分です

このADOオブジェクトは、FileSystemObjectと同じようなインスタンスを作成して利用することのできるオブジェクトになります

Accessデータベースのファイルを作成し、接続を行い、テーブルの作成を行ったら
次に行うのがこのオブジェクトの作成です
それからようやくレコード操作が可能になります
実際のコードにすれば、ほんの数行の話なのであまり気負うものでもありませんが、初めは戸惑いたっぷりになるとは思いますが、基本はセルなどのオブジェクト操作と変わりませんので、この記事を読んでいる方のレベルなら特段難しくはありません

と、ここまでExcelVBAのオブジェクト利用の優位性を言い過ぎましたが、上記でも言いましたが実行回数が同じであれば確かにオブジェクト利用が優位です
ですが、オブジェクト利用ではレコードを1つずつ操作することになります

データベース全体に対するレコード操作を行いたい場合に1000レコードのデータベースであれば1000回コードを実行することになります
ですが、SQLのUPDATE文を利用すればたった1回の構文でデータベース全体の編集を行うことが出来ます

こうなるとコードの可読性においてもこちらが優位です

つまり言いたいのは、ADOオブジェクトだけではレコード操作を有効利用できない、ということです
おすすめ書籍でもあげているSQLの基礎本などを参考にSQLもすこしずつ理解を深めていってください

フィルタやワークシート関数を多用して、かつピボットテーブルまで使ってようやく出来るような集計がSQLでは驚くほど簡単に出来ることが多いです
データベースをデータの保管庫としてだけでなく、集計処理でも有効にデータベースを利用してください

データベースに接続・切断する(ADODB.Connection)

ADODB.Connectionオブジェクトを利用してデータベースへの接続と切断を行うコード

'accdbファイルに接続する
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

'接続を解除(切断)する
zzDB_Con.Close

データベースの作成が完了したら、次にデータベースにOpenメソッドを使用して接続することで操作が可能となります
また、接続を行うとデータベースが接続状態となります、この状態を明示的に解除するのがCloseメソッドになります

データベースは性質上、排他的に開いたりすることが出来ます
データベースを操作する場合は、この切断処理は必ず行うようにしてください

なおコード中の「zzDB_Con」はインスタンスを作成したObject型の変数です
詳細に関しては以下の記事を確認してください

コード解説

zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

Openメソッドを使用して、データベースに接続を開始します
引数のProviderには既定の文字列を指定します、accdbファイルでは例コードの文字列を基本的に使用します
そのあとに続くSourceにAccessデータベースファイルの絶対パスを文字列で指定します
今回の場合では、同じフォルダ内のデータを参照しています

このOpenメソッド以降で、データベースに関する操作を行うことが出来るようになります

空白のファイルを作成して、最初に接続した場合はさらにテーブルの作成を行う必要があります
このテーブルの作成まで行うと、そこにレコードを登録していくことが出来るようになるためデータベースにデータを保存していくことが出来ます

zzDB_Con.Close

このコードを実行すると接続しているデータベースを切断します
データベースの利用が終了したら確実に行います
また、なるべくデータベースは無駄に接続することの無いようにしてください

データベースから受け取ったデータの加工等に時間のかかる処理であれば、処理後にまたデータベースを利用するとしても、いったん接続は切断するようにします

ただ、処理に時間が大きくかかる可能性が無いことや、データベースの利用自体がそもそも個人レベルのものである場合はそこまで気にしなくてもいいです

なんせ、接続したら終了時には切断する
ここをしっかり行うようにしてください

データベース接続用オブジェクトについて(ADODB.Connection)

データベースに接続するためのADODB.Connectionオブジェクトの宣言コード

'接続用オブジェクトの作成
Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")

Set zzDB_Con = Nothing

Accessデータベースを操作するには、まずデータベースファイルの作成を行う必要がありました

次に必要なのが、そのデータベースと接続を行うことです
この接続という操作を行って初めてデータベースの操作が可能になります

その際に使用するのが記事コードのADODB.Connectionオブジェクトになります
これはインスタンスの作成を行う必要がありますので、最初にCreateObject関数でインスタンスの作成を行います

このオブジェクトは単にデータベースとの接続だけを行うものでは無く、接続したデータベースにSQL文を実行することが出来ます
基本的には接続とSQL文の実行の2つを実行するオブジェクトと認識してください

そしてSQL文の実行に関して、このオブジェクトではレコードを取得する必要が無い場合に主に利用します

例えば、UPDATE文はデータベースのデータを更新しますが、結果を取得しません
そういった構文に利用します
なのでSELECT文のようなレコードの取得を行う構文はこのオブジェクトでは利用できません

コード解説

Dim zzDB_Con As Object

ADODB.Connectionを代入させる変数です
事前バインディングの場合は専用の型を使用しますが、実行時バインディングを利用しますのでここではObject型で指定します

Set zzDB_Con = CreateObject("ADODB.Connection")

CreateObject関数でインスタンスの作成を行っています
Class名はADODB.Connectionと指定します

この作成後にデータベースの操作を行います

Set zzDB_Con = Nothing

操作が完了したらオブジェクト変数なので、最後に解放して初期化しておきます

実際の使用について

これで作成を実行した後に、使用するメソッドは主に3つになります
まずはデータベースとの接続を行うOpenメソッド接続を解除するCloseメソッドSQL文の実行を行うExecuteメソッドになります

OpenメソッドとCloseメソッドはセットで使用します
Openメソッド使用後はデータベースが使用中になります
この状態を解除するのがCloseメソッドなので必ず使用してください

Executeメソッドは、引数にSQL文を文字列で指定することで接続しているデータベースに対して実行します
上記にも解説したようにレコードの取得を必要としない構文を実行します

Accessデータベースを操作するうえで利用するのは、あとレコードを代入させるためのADODB.Recordsetオブジェクトを使用します

ちなみに、ここで使用しているADODB以外にDAOというオブジェクトもありますが、こちらはADODBより古いオブジェクトになります
こちらはこちらで利点もありますし、使用感も似ていますが両方を覚える必要はありません
せめてインスタンス作成が同じで利用できるなら良いのですが、別のインスタンスを作成する必要があるので両方の利用は面倒ですね

Accessデータベースの操作について

ExcelVBAでAccessデータベースを操作する流れ

Excelは今でこそ100万行のデータ行数に、凄まじい速さとなった関数などのおかげで大容量データもストレスなく扱えるようになりました
2003時代では数メガのデータ容量となったExcelファイルは保存するだけでストレスをよく感じたものです

そんな時代によく聞いたのが、容量の問題でExcelに限界を感じて、Accessに移行したという話です
やはり当時数千行データとなると、どうしてもこの話に勝てませんでした

とにかく、わしのPCにAccess入ってへんから知らんがな、と強がりを言ったものですが、数千行のデータとなってもストレスフリーなExcelに育ったのも少し感慨深いものです

さて、脱線しましたが、ここで言いたいのは現在の状況であってもやはりデータの保管という点においてAccessデータベースには敵わないという点です
いくら100万行あるとはいっても、さすがに数万というデータになるときつくなってきます

とはいえ保管されたデータを見やすくする点でExcelにはAccessは全く敵いません
今はPowerBIというアプリもありますが、まだまだExcelの加工・グラフは第一線でしょう

つまり、大容量データの保管をAccessデータベースに、データの利用にはExcelにというのが最大の利用法になります

そしてこの利用法において、最大級にExcelに軍配が上がります
なぜなら、ExcelVBAを利用することで、データベースの作成・操作が可能だからです
データベースの操作を行いつつ、Excel上で加工もできる
Accessが無くても問題はありません、Excelだけで可能です
逆にAccessはあるけど、Excelが無い人はこれが出来ません
今は強がりではなく、Accessは知らんExcelで十分と言えます

これを言うとAccessファンの人の癇に障るかもしれませんが、自分はExcelファンでここに来ている人もきっとそうなので問題なしとしておきましょう

操作の流れ

ExcelVBAでデータベースを操作する場合の流れとしては、大きく2通りあります

1つ目はデータベースファイル、accdbファイルを作成し、そこにテーブルを作成してレコードを登録、加工や集計を行う
必要に応じてワークシートに出力する

2つ目はデータベースファイルを介さず、メモリ上にデータベースを作成し、同じく登録等を行い、ワークシートに出力する

この2つの流れになります
2つ目の方法は少し難しくなりますが、処理の動きをイメージできるようになれば加工・集計にSQLを利用することが出来るようになるため処理の幅が広がります

基本的には1つ目のデータベースファイルを作成して操作を行います
この2つの違いは、データベースに登録したレコードを保存するかしないかという点で考えてください
保存する必要がある場合は1つ目、加工・集計のみであれば2つ目の流れになります

とはいえ、2つ目の流れは1つ目の流れが包括しているのでこのAccessデータベース操作が慣れてきたら挑戦してみてください

まずは以下の記事からデータベースの作成を行ってください

作成が行えたら次に作成するのがテーブルです
テーブルとはExcelで言うテーブル機能のようなものです

Accessデータベース操作はレコードセットを利用したり、SQLを利用するため少しレベルが上がる内容ですが
ここも使えるようになると、ビッグデータが怖くなくなるので1年1ファイルなんて言うケチ臭いことは考えなくて良くなります

ExcelVBAで出来ることの限界を極めるためにここをやりきってみてください

SQLについて

データベース操作において、必ず使用することになるものです
これはオブジェクト操作ではなく、別の言語です

データベースを操作するための言語なのですが、これがなかなか大変なのが扱うデータベースの種類によって微妙な差がある点です

そしてAccessデータベースで使用するSQLはMicrosoft Access SQLという言語を利用します

OracleやMySQL、Microsoft SQL Server等々の有名な言語がありますが、別物です
特にMicrosoft SQL ServerとMicrosoft Access SQLは別なので混同しないようにしてください

このAccessデータベース操作をやり始めた時に、ここの理解が全く出来ておらず
ビューの作成を行い、そのビューの構成を変更したくて「ALTER VIEW」がエラーとなった際に全く原因が分かりませんでした
Microsoft Access SQLにはこのコードがサポートされていません

そこで初めて、いま使っているSQLは一体どの種類のモノなのか?
という疑問点を長く持ち続けて、ようやく気付いたのがMicrosoft Access SQLというSQLを使用していることでした

ほとんどは他のSQLと差は無くエラーが発生することはありません
なのであまり大きく意識をする必要もなかったというのもあります
また、このSQLを明確に記載しているSQLの書籍を見たことがありませんでしたし、このSQLの専門書もありません
しかし、今はMicrosoftのヘルプサイトが充実しているのである程度何とかなります

他の言語との微妙な違いではデータ登録時の点がいくつかあります

文字列を表す場合、多くのSQLでは’(シングルクォーテーション)を使用しますが、こちらでは”(ダブルクォーテーション)も利用できます
日付データ時には’(シングルクォーテーション)か#で挟むかのどちらも利用可能です
自分は#を使用します、これはVBAの仕様と一致するからです

ワイルドカードでも少し差があります
Excelは通常、任意の1文字を「?」、0以上文字を「*」で表現します
Microsoft Access SQLでは、任意の1文字を「_」(アンダースコア)、0以上文字を「%」で表現します

なお任意の1文字は「?」、0以上文字は「\*」も利用可能なのですが、これも混乱しました
Excel通常の「?」が使えるのに「*」が使えないのはなんでやねん、となりましたから

データ型に関しても独特です
本来SQLの型にString型は存在しません、CHARやTEXTが文字列型になります
しかし類義語という指定に含まれており、自動的に変換されておりVBAからString型を指定してもエラーは発生しません
ここがVBA使いとしては楽な点です、コードの可読性を下げることなく型指定を行うことが出来る訳ですから
本来は正式な型宣言を行うべきなのでしょうが、まあ自動的に変換してくれてるんだから有効利用しましょう

この辺り含め詳細に関してはヘルプサイトを参照してください
Microsoft Access SQL リファレンスはこちらから

最後に、SQLの勉強に関してですが
ここはそれほど複雑な構文を作成する必要はありませんので、基本の書籍で十分です
おすすめ書籍に記載しているSQLの絵本という書籍があるのですが、ExcelVBAからの利用の観点で行けば十分すぎる内容がありますのでお勧めします
ちなみにMicrosoft Access SQLについての記載は無いので、微妙な違いの点についてはリファレンスと合わせて確認を進めてください

Accessデータベースの空ファイルの作成

Accessが無い環境でAccessファイルを作成するコード

'実行ファイルと同じ場所に空のAccessファイルを作成
Dim zzDBAdox As Object
Set zzDBAdox = CreateObject("ADOX.Catalog")
zzDBAdox.Create "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"
Set zzDBAdox = Nothing

Accessデータベースの2007以降のバージョンである、accdbファイルを処理実行ファイルと同じ場所に新規作成します
同じ名称のファイルが存在する場合は実行時エラーが発生します

基本的にこのコードはAccessがPCに入っていない環境でデータベースを扱う際に使用します
Accessがあるなら、Accessで作成してもらってもいいです

コード解説

Dim zzDBAdox As Object

Accessデータベースを作成するには、ExcelVBAの標準コードでは実行できません
ADOX.Catalogというものを使用しますので、これのインスタンスの作成を先に行う必要がありますので、それを代入させるための変数宣言です

事前バインディングであれば、「Microsoft ADO Ext. 6.0 for DDL and Security」を参照設定して「ADOX.Catalog」の型指定を行うことでインテリセンスを使用できます
参照設定の数字の部分は使用するバージョンによって差があります

ただ、このオブジェクトではデータベースを作成する以外にテーブルの設定などを取得できますが、ほぼこの作成機能しか使用しないのでそこまではしなくてもいいとも思います

Set zzDBAdox = CreateObject("ADOX.Catalog")

実行時バインディングを実行しています
その際に使用するのが「ADOX.Catalog」という文字列になります
これで機能を使用することが出来る状態になりました

zzDBAdox.Create "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

実際のファイル作成のコードになります
Createメソッドを使用することでファイルの作成を行えます

通常のExcelのファイルを作成するように、ただファイルの絶対パスを指定するだけでは作成できません

Providerという引数を指定する必要があります
これに関してはAccessのバージョンによって違いがあります

「Microsoft.Ace.OLEDB.12.0」というのはAccessの2007以降のaccdbファイルを利用する場合に指定する文字列です

ここに関してはOfficeの上位互換を考慮すると、それほど意識する必要はありません
実際、2019でも365でも12の利用に問題はありません
これの上位に「Microsoft.Ace.OLEDB.16.0」というものがあります
主にBit環境に依存するようですので、もし接続エラーが発生する場合に切り替えるようにすればいいと思います
正直Bit依存の対応は永久的な方法は存在しないので、あまり考えてもしょうがないです、ExcelVBAの業務改善はその時その場面での利用が多いからです
WinAPIの利用時と同じようなイメージということになります

このProvider文字列は作成時だけでなく、接続時にも使用します
接続時とは、データベースを操作するタイミングのことです
なので、以降もよく出てくるので広域常数やモジュールレベル常数で作成しておけば良いです

この接続文字の後にファイルの絶対パスを指定します

ファイルの拡張子はaccdbを指定してください
Accessのデータベースファイルには、これ以前にmdbファイルというものがあります
ただこの拡張子はもうaccdbに代わって10年以上経っています
なので本サイトではここ以外では扱いません

なお、mdbファイルの作成及び接続には以下の文字列を使用します
「Microsoft.Jet.OLEDB.4.0」で接続して、ファイルの拡張子はmdbとします

接続後の操作に関しては全く同じです

Set zzDBAdox = Nothing

オブジェクト変数を使用しているので、最後に解放します

作成後について

これでファイルの作成が完了です
ただし空のファイルなので、テーブルも存在していません
テーブルというのはデータを収める場所です

イメージ的には全くシートの存在しないExcelファイルを作成しただけで、シートを作成しないとデータを入力することが出来ない状態というようなイメージです

テーブルの作成もレコードの操作もExcelVBAから行えますので、そちらを行ってください

また、このファイル作成を一時ファイルとして利用することも出来ます
というのも、データベースでの最大の利点がSQLを利用することが出来る点になります
これを利用すると、集計やデータ全体の加工などがExcel上で扱うより楽に出来たり、処理速度の向上に繋げることも出来ます

なので、このコードで一時ファイルを作成してデータベースを登録して加工・集計してワークシートに出力して、一時ファイルを削除する

という流れの処理を作成することも出来ます、ExcelVBAにSQLを加えることが出来ます

もし大容量データを加工・集計して、別のシートやブックに転記するような処理があった場合は検討してみてください