How to Import Excel Data into Microsoft Access Database

Moving data from Excel into Access is a common need: one-time migrations, recurring loads, or feeding an Access app from spreadsheet-based processes. Doing it wrong leads to type mismatches, broken dates, and duplicate or missing rows. This guide covers three ways to import Excel data into a Microsoft Access database—the Import Wizard, linked tables, and VBA—plus data mapping, pitfalls, and when to link instead of import. For table design first, see create a table in MS Access.

Why Import Excel Data into a Microsoft Access Database?

Access gives you relational structure, validation, multi-user access, and reporting that Excel cannot. Importing Excel data into a Microsoft Access database lets you consolidate spreadsheets into tables, enforce data types and keys, and build queries and forms on top. One-time imports suit migrations; linked Excel tables suit read-only or occasionally refreshed data; VBA or saved import specs suit repeated imports (e.g. monthly files). Choose the method that matches how often the data changes and who owns the source file.

Three Ways to Get Excel Data into Microsoft Access

Import Wizard (one-time or manual): External Data → New Data Source → From File → Excel. Pick the workbook and sheet; map columns to a new or existing table. Best for ad-hoc or one-off imports.
Linked table: External Data → Excel → Link to the data source. Access reads the Excel file each time you open the table or run a query. No copy in Access; good when Excel is the source of truth and you only need to read.
VBA (DoCmd.TransferSpreadsheet): Run imports from code: same file path repeatedly or loop over files. Use when imports are recurring, automated, or part of a larger process. See our Access VBA and expand Excel functionality guides.

Step-by-Step: Import Excel Data into Access Using the Wizard

Use this when you want to copy Excel data into a native Access table (new or existing).

Prepare the Excel file: First row as column headers; no merged cells in the data range. Remove blank rows and columns from the range you want to import. Dates in a consistent format (e.g. MM/DD/YYYY or ISO).
Start the import: In Access: External Data tab → New Data Source → From File → Excel. Browse to the .xlsx (or .xls) file.
Choose the sheet or range: Select the worksheet or named range. Preview the columns; ensure the first row is used as field names.
Map to a table: Create a new table or append to an existing one. If the target table exists, match column names or order. Access will map by position if you do not specify; check data types in Design View after.
Finish and verify: Run the import. Open the table in Datasheet View; check row count, date columns, and numeric fields. Fix any type mismatches in Design View and re-import if needed.

Import Excel Data into Access Using VBA (TransferSpreadsheet)

For recurring imports—e.g. the same file path updated monthly—use DoCmd.TransferSpreadsheet. You can call it from a button, macro, or scheduled run. acImport imports into an existing table; the table must exist and column order/types should match the spreadsheet.

Public Sub ImportExcelToAccess()
    Dim strPath As String
    Dim strTable As String
    strPath = "C:DataSalesReport.xlsx"
    strTable = "tblSalesImport"
    On Error GoTo Err_Import
    DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        TableName:=strTable, _
        FileName:=strPath, _
        HasFieldNames:=True, _
        Range:="Sheet1$A1:Z1000"
    MsgBox "Import complete.", vbInformation
    Exit Sub
Err_Import:
    MsgBox "Import failed: " & Err.Description, vbCritical
End Sub

HasFieldNames:=True uses the first row as column names. Range is optional; omit it to import the whole sheet. For multiple sheets or files, loop with Dir() or a list and call TransferSpreadsheet for each.

Data Type Mapping When You Import Excel into Access

Excel does not enforce types the way Access does. On import, Access guesses types from the first rows—so mixed text and numbers in a column can become Short Text or drop values. Dates can shift if the format is ambiguous. Best practice: create the Access table first (see create a table in MS Access and data types in MS Access), set the correct field types and sizes, then import and map. For append imports, ensure column order and types align; use the wizard once to generate a saved import spec if you run it repeatedly.

Common Mistakes When Importing Excel Data into Access

Headers or merged cells in the data: Excel ranges with merged cells or multiple header rows break column alignment. Flatten to one header row and contiguous data.
Date and number formats: Regional settings (US vs UK dates, decimal comma) can misalign dates and decimals. Normalize in Excel before import or use a consistent format.
Appending without checking duplicates: Repeated imports into the same table can duplicate rows. Use a unique key and run a find-duplicates query, or delete/recreate the table before each import.
Importing when you should link: If the Excel file is updated often and you only need to read it, link instead of import so you always see current data without re-importing.

When to Link Excel Instead of Importing into Access

Link to the Excel file when the data is owned in Excel and changes frequently, or when you do not want to store a copy in Access. Linked Excel tables are read-only in typical use; queries and reports run against the live file. Linking avoids duplicate data but can be slower and more fragile (path changes, file open by another user). For one-time or periodic snapshots, import into a local table and use Access queries for reporting.

Key Takeaways

Import Excel data into a Microsoft Access database via the Import Wizard (one-off), linked table (read-only, live Excel), or VBA TransferSpreadsheet (recurring/automated).
Prepare Excel: one header row, no merged cells, consistent dates and numbers. Create the Access table with correct types first when possible.
DoCmd.TransferSpreadsheet with acImport and HasFieldNames:=True is the standard VBA pattern for programmatic import.
Avoid duplicate rows on append by using a key and de-duping, or by replacing the table. Link instead of import when you need live Excel data without copying.

Frequently Asked Questions

How do I import Excel data into a Microsoft Access database?

Use External Data → New Data Source → From File → Excel, then choose the workbook and sheet. Map columns to a new or existing table. For repeated imports, use VBA DoCmd.TransferSpreadsheet with acImport and the table name and file path.

Can I link Excel to Access instead of importing?

Yes. Use External Data → Excel → Link to the data source. Access will read the Excel file when you open the linked table or run queries. Data stays in Excel; no copy is stored in Access. Best when Excel is updated often and you only need to read.

Why do my dates or numbers look wrong after importing Excel into Access?

Access infers types from the first rows. Mixed text and numbers, or ambiguous date formats (e.g. MM/DD vs DD/MM), cause wrong types. Create the Access table first with correct Date/Time or Number fields, then import and map. Use consistent date formats in Excel.

How do I automate importing Excel into Access?

Use VBA: DoCmd.TransferSpreadsheet TransferType:=acImport, TableName:="YourTable", FileName:=path, HasFieldNames:=True. Call this from a button, macro, or scheduled task. For multiple files, loop over paths and run TransferSpreadsheet for each.

What is the best way to avoid duplicate rows when importing Excel into Access?

Either import into an empty table each time (e.g. delete rows or drop/recreate the table before import) or append only and then run a find-duplicates query and delete duplicates by a unique key. Define a primary key or unique index on the target table when possible.

Conclusion and Next Steps

Importing Excel data into a Microsoft Access database is straightforward when you prepare the spreadsheet, choose import vs link, and map types correctly. Use the wizard for one-off moves, linked tables for live Excel data, and VBA for recurring imports. After data is in Access, build queries and forms on top; for table design and data types, see understanding tables in MS Access and Microsoft Access database tutorial. If you need help designing an import process or cleaning legacy Excel data for Access, contact us for a free quote.