【Excel VBA】Accessにテーブルを新規作成し、Excelからデータを流し込む方法

2025年8月16日

Excel VBAからAccessを操作する際、既存のテーブルにレコードを追加するだけでなく、テーブルそのものをVBAで新しく作成したい、という場合があります。例えば、毎月の集計結果を、月ごとの新しいテーブルとしてAccessに保存していくようなケースです。

この記事では、**DAO (Data Access Objects)**を使い、Accessデータベース内に新しいテーブルを定義・作成し、続けてExcelシート上のデータをその新テーブルに流し込むまでの一連の処理を自動化する方法を解説します。

【重要】実行前の参照設定

DAOの機能、特にdbLongやdbTextといったデータ型を定義するための「定数」をVBAで利用するには、事前に**「参照設定」**が必要です。

VBE（Alt+F11）を開き、メニューの ツール > 参照設定 をクリックします。
一覧の中から**「Microsoft Office XX.0 Access database engine Object Library」**を探し、チェックを入れて「OK」をクリックします。

完成したVBAコード

以下が、テーブル作成からデータ追加までを行うVBAコードです。コードを再実行した際にエラーにならないよう、最初に同名のテーブルがあれば削除する処理も加えています。

'参照設定: Microsoft Office XX.0 Access database engine Object Library
Sub CreateTableAndImportData()

    ' DAOオブジェクト用の変数を宣言
    Dim db As DAO.Database
    Dim tblDef As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim dbPath As String
    Dim sourceRange As Range
    Dim i As Long
    
    ' --- 1. データベースに接続 ---
    dbPath = ThisWorkbook.Path & "\ProductDB.accdb"
    Set db = DBEngine.OpenDatabase(dbPath)
    
    ' --- 2. テーブルの作成 ---
    ' もし同名のテーブルが存在していたら削除する (エラー防止)
    On Error Resume Next
    db.TableDefs.Delete "T_Product_Category"
    On Error GoTo 0
    
    ' 新しいテーブル定義を作成
    Set tblDef = db.CreateTableDef("T_Product_Category")
    
    ' フィールド(列)を定義して追加
    With tblDef
        .Fields.Append .CreateField("CategoryID", dbLong)
        .Fields.Append .CreateField("CategoryName", dbText, 50) 'サイズ50のテキスト型
    End With
    
    ' データベースにテーブル定義を追加して、テーブルを確定
    db.TableDefs.Append tblDef
    
    ' --- 3. Excelからデータを読み込み、新テーブルに追加 ---
    ' データを追加するためにレコードセットを開く
    Set rs = db.OpenRecordset("T_Product_Category")
    
    ' Excelのデータ範囲を設定
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:B5")
    
    ' 2行目から最終行までループしてレコードを追加
    For i = 2 To sourceRange.Rows.Count
        rs.AddNew
        rs!CategoryID = sourceRange.Cells(i, 1).Value
        rs!CategoryName = sourceRange.Cells(i, 2).Value
        rs.Update
    Next i
    
    ' --- 4. 後片付け ---
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set tblDef = Nothing
    Set db = Nothing

    MsgBox "Accessへのテーブル作成とデータ追加が完了しました。"
End Sub

コードのポイント解説（前半：テーブル作成）

① テーブル存在確認と事前削除（推奨）

On Error Resume Next
db.TableDefs.Delete "T_Product_Category"
On Error GoTo 0

このマクロを何度も実行すると、2回目以降は「同じ名前のテーブルが既に存在します」というエラーが発生します。それを防ぐため、On Error Resume Nextでエラーを一時的に無視させ、db.TableDefs.Deleteでテーブルの削除を試みています。これにより、常にまっさらな状態からテーブルを作成できます。

② テーブル定義の作成 (.CreateTableDef)

Set tblDef = db.CreateTableDef("T_Product_Category")

db.CreateTableDefメソッドで、新しいテーブルの「設計図」となるTableDefオブジェクトを生成します。引数には、作成したいテーブルの名前を指定します。

③ フィールド（列）の定義と追加 (.CreateField)

.Fields.Append .CreateField("CategoryID", dbLong)
.Fields.Append .CreateField("CategoryName", dbText, 50)

.CreateFieldメソッドで、テーブルの列（フィールド）を一つずつ定義します。

第一引数: フィールド名（例: "CategoryID"）
第二引数: データ型（例: dbLongは長整数型、dbTextはテキスト型）
第三引数（任意）: テキスト型の場合の最大文字数など

作成したフィールドは、.Fields.Appendメソッドでテーブル定義に追加していきます。

④ テーブルの確定 (.TableDefs.Append)

db.TableDefs.Append tblDef

全てのフィールドを定義し終えたら、完成したテーブルの設計図（tblDef）を、データベースのテーブルコレクション（db.TableDefs）に追加します。この命令が実行された時点で、Accessデータベース内に物理的にテーブルが作成されます。

コードのポイント解説（後半：データ追加）

テーブル作成後、後半部分では新しく作成したテーブルにExcelからデータを流し込んでいます。

Set rs = db.OpenRecordset("T_Product_Category")
For i = 2 To sourceRange.Rows.Count
    rs.AddNew
    rs!CategoryID = sourceRange.Cells(i, 1).Value
    rs!CategoryName = sourceRange.Cells(i, 2).Value
    rs.Update
Next i

この部分は、レコードを追加する際の基本的な流れと同じです。OpenRecordsetで今しがた作成したテーブルを開き、Forループの中でAddNewとUpdateを繰り返して、Excelシートのデータを1行ずつテーブルに追加しています。