[Excel VBA] How to Create a New Access Table and Import Data

Introduction

When manipulating Access from Excel VBA, you may encounter situations where you need to create a new table from scratch rather than just adding records to an existing one. A common example is saving monthly aggregation results as separate tables in Access.

In this article, I will explain how to use DAO (Data Access Objects) to automate the process of defining a new table structure in an Access database and immediately importing data from an Excel sheet into that new table.

Important: Setting References

To use DAO features in VBA, specifically constants like dbLong and dbText, you must set a Reference beforehand.

1. Open the Visual Basic Editor (Alt + F11).
2. Go to Tools > References in the menu.
3. Find “Microsoft Office XX.0 Access database engine Object Library” (where XX is the version number), check the box, and click OK.

Complete VBA Code

The following code handles everything from creating the table to adding the data. To prevent errors when re-running the macro, it includes a step to delete the table if it already exists.

' Reference: Microsoft Office XX.0 Access database engine Object Library
Sub CreateTableAndImportData()

    ' Declare variables for DAO objects
    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. Connect to the database ---
    ' Update the path to match your environment
    dbPath = ThisWorkbook.Path & "\ProductDB.accdb"
    Set db = DBEngine.OpenDatabase(dbPath)
    
    ' --- 2. Create the Table ---
    ' Delete the table if it already exists (to prevent errors)
    On Error Resume Next
    db.TableDefs.Delete "T_Product_Category"
    On Error GoTo 0
    
    ' Create a new table definition
    Set tblDef = db.CreateTableDef("T_Product_Category")
    
    ' Define and append fields (columns)
    With tblDef
        .Fields.Append .CreateField("CategoryID", dbLong)
        .Fields.Append .CreateField("CategoryName", dbText, 50) ' Text type with size 50
    End With
    
    ' Append the table definition to the database to finalize creation
    db.TableDefs.Append tblDef
    
    ' --- 3. Read data from Excel and add to the new table ---
    ' Open a recordset for the newly created table
    Set rs = db.OpenRecordset("T_Product_Category")
    
    ' Set the data range in Excel
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:B5")
    
    ' Loop from the 2nd row to the last row to add records
    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. Cleanup ---
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set tblDef = Nothing
    Set db = Nothing

    MsgBox "Table creation and data import to Access complete."
End Sub

Code Explanation: Creating the Table

1. Checking for Existing Tables

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

If you run this macro twice, it will fail the second time because a table with the same name already exists. To prevent this, we use On Error Resume Next to ignore errors temporarily and attempt to delete the table using db.TableDefs.Delete. This ensures we always start with a clean slate.

2. Creating the Table Definition

Set tblDef = db.CreateTableDef("T_Product_Category")

The CreateTableDef method creates a TableDef object, which acts as the “blueprint” for the new table.

3. Defining Fields (Columns)

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

We define columns one by one using the .CreateField method.

• 1st Argument: Field Name (e.g., “CategoryID”)
• 2nd Argument: Data Type (e.g., dbLong for Long Integer, dbText for Text)
• 3rd Argument (Optional): Size (for Text types)

4. Finalizing the Table

db.TableDefs.Append tblDef

Once the blueprint is complete, we add it to the database’s TableDefs collection. The table is physically created in the Access database only after this command is executed.

Code Explanation: Importing Data

After creating the table, the code imports data from 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

This follows the standard procedure for adding records:

1. Open the table using OpenRecordset.
2. Loop through the Excel rows.
3. Use AddNew to create a buffer.
4. Set the values.
5. Save the record using Update.

Summary

Dynamically creating Access tables using VBA involves two main phases:

1. Definition Phase: Use CreateTableDef and CreateField to design the table structure.
2. Data Manipulation Phase: Use OpenRecordset and AddNew/Update to populate the table.

Mastering this flow allows you to build highly flexible automation tools that use Excel as a hub for database management.