Expert guide

Connect MS Access with Excel

Manual linking, OLE DB connections, TransferSpreadsheet, and ADODB VBA-from both sides

Get a Free Estimate

Most businesses already store operational data in Microsoft Access and analyze it in Excel. The problem is not whether the tools can talk to each other-they can. The problem is choosing the right connection method: a one-time export, a refreshable link, or a VBA automation layer that runs on a schedule without anyone opening files manually.

This guide walks through every practical approach we use in client projects: manual export from Access, Excel’s Get Data connection to an .accdb file, linked Excel sheets inside Access, DoCmd.TransferSpreadsheet, and ADODB recordset code from Access VBA and Excel VBA. You will see real connection strings, common failure points, and when to call in a developer instead of fighting broken drivers on a Monday morning.

Choose the right connection pattern first

Before writing code, decide which system owns the data and whether Excel is a reporting destination or a two-way editing surface.

  • Access → Excel (reporting)

    Access holds tables and queries; Excel receives snapshots or refreshable queries for pivots, charts, and distribution. Best for finance, operations, and management dashboards.

  • Excel → Access (collection)

    Users fill structured Excel templates; VBA or linked imports load rows into Access tables with validation. Common for forecasts, timesheets, and distributed data entry.

  • Bidirectional (advanced)

    Controlled write-back from Excel into specific Access tables-only with primary keys, validation, and audit logging. Avoid letting users edit linked tables directly in Excel.

If more than five people need concurrent edits, consider moving the Access backend to SQL Server and linking Excel to SQL instead-see our guide on migrating Access to SQL Server. For most small teams, Access plus Excel remains fast and cost-effective.

Method 1: Manual export from Access (no code)

The built-in export is the fastest way to prove a query works before you automate it. Use it for ad-hoc analysis, not recurring month-end reporting.

Export a table or query to a new workbook

  • Open the Navigation Pane

    In Access, locate the table or saved query you want in Excel (queries are usually better-they apply filters and joins).

  • External Data → Export

    Right-click the object → Export → Excel. Or select it and use External Data → Export → Excel on the ribbon.

  • Choose destination

    Pick a file path and whether to open the file when finished. Access creates a new .xlsx with a worksheet named after the object.

  • Formatting

    Access exports raw values. Apply number formats, column widths, and print areas in Excel-or automate that later with VBA.

Limitations: no automatic refresh, no parameter prompts carried into Excel, and large tables can be slow. For recurring reports, move to Method 4 or Method 5.

Method 2: Link Excel to Access with Get Data (no VBA)

Excel can treat an Access database like any other external data source. The connection is refreshable from the Data tab-ideal for analysts who live in Excel but should not open Access daily.

Excel 365 / Excel 2021 steps

  • Data → Get Data

    From Database → From Microsoft Access Database. Browse to your .accdb or .mdb file.

  • Navigator

    Select tables or queries. Use Load for a static copy, or Load To → PivotTable/PivotChart/Connection Only as needed.

  • Refresh

    Right-click the query table → Refresh, or Data → Refresh All. Excel re-runs the Access query and replaces rows.

  • Credentials

    On a network share, ensure the file path is identical for all users (mapped drive letter vs UNC path breaks refresh on other PCs).

Behind the scenes Excel uses the ACE OLE DB provider (Microsoft.ACE.OLEDB.12.0). Every machine that refreshes needs the same bitness: 64-bit Excel requires 64-bit Access Database Engine. Mixed Office installs are the number-one reason “it works on my PC” failures happen.

Method 3: Link or import Excel into Access

When Excel is the source (budget templates, price lists), Access can link to the workbook so forms and reports read live Excel data, or import a one-time copy into a native Access table.

  • External Data → New Data Source

    File → New Data Source → From File → Excel. Select the workbook.

  • Link vs Import

    Link keeps data in Excel (globe icon on the table). Import copies data into Access-better for performance and referential integrity once the template is approved.

  • First row contains column headings

    Check this if row 1 is headers. Access infers data types from the first rows-clean the sheet first (no blank header columns, consistent dates).

  • Named ranges

    You can link to a specific Excel named range instead of an entire sheet if templates use defined names like tblSalesInput.

Linked Excel tables are read-only in Access unless you use specific updateable linked table scenarios; for write-back, use VBA import procedures with error handling instead of hoping users won’t break links.

Method 4: DoCmd.TransferSpreadsheet (Access VBA)

TransferSpreadsheet is the quickest automation when you need a full table or query dumped to .xlsx with minimal code. We use it for nightly exports before layering formatting in a second step.

Export a query to Excel (overwrite file)

Public Sub ExportSalesReport()
    Const acExport As Long = 10
    Const acSpreadsheetTypeExcel12Xml As Long = 10
    Dim strPath As String

    strPath = "C:\Reports\SalesReport_" & Format(Date, "yyyymmdd") & ".xlsx"

    DoCmd.TransferSpreadsheet _
        TransferType:=acExport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="qrySalesByRegion", _
        FileName:=strPath, _
        HasFieldNames:=True

    ' Optional: open Excel for the user
    CreateObject("Excel.Application").Workbooks.Open strPath
End Sub

TableName can be a table or query name. For multiple exports, loop query names and build unique file paths. TransferSpreadsheet does not apply Excel formatting, charts, or template placement-you typically open a master .xlsx and paste values into a named sheet with Excel VBA, or use CopyFromRecordset (Method 6).

Method 5: ADODB from Access VBA (query → Excel sheet)

ADODB (ActiveX Data Objects) gives you control: parameters, WHERE clauses, row-by-row logic, and writing into an existing Excel template. This is what we deploy when TransferSpreadsheet is too blunt.

Reference setup in Access

  • VBA Editor → Tools → References

    Enable Microsoft ActiveX Data Objects Library (pick 6.1 if available). Also reference Microsoft Excel xx.x Object Library if you automate Excel directly.

  • Early vs late binding

    Early binding (Dim xl As Excel.Application) gives IntelliSense; late binding (CreateObject) avoids version issues when Excel upgrades. Production add-ins often use late binding for Excel and early binding for ADODB.

Push an Access recordset into a new Excel workbook

Public Sub AccessRecordsetToExcel()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xl As Object
    Dim wb As Object
    Dim ws As Object
    Dim lngRow As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT Region, OrderDate, Amount FROM qrySales WHERE OrderDate >= Date()-30", dbOpenSnapshot)

    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set wb = xl.Workbooks.Add
    Set ws = wb.Worksheets(1)

    ws.Cells(1, 1).Value = "Region"
    ws.Cells(1, 2).Value = "OrderDate"
    ws.Cells(1, 3).Value = "Amount"
    ws.Range("A1:C1").Font.Bold = True

    lngRow = 2
    Do While Not rs.EOF
        ws.Cells(lngRow, 1).Value = rs!Region
        ws.Cells(lngRow, 2).Value = rs!OrderDate
        ws.Cells(lngRow, 3).Value = rs!Amount
        lngRow = lngRow + 1
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    ws.Columns("A:C").AutoFit
End Sub

For large datasets (50k+ rows), prefer CopyFromRecordset or writing to a CSV first-cell-by-cell loops are slow. For template-based reports, open an existing workbook and target a named range instead of Cells(row, col).

Read Excel into Access with ADODB (ACE OLE DB)

You can query an Excel worksheet as if it were a table. HDR=YES treats the first row as column names. IMEX=1 helps when columns mix text and numbers.

Import rows from Sheet1 into an Access table

Public Sub ImportExcelSheetToAccess()
    Dim cn As Object
    Dim rs As Object
    Dim strExcel As String
    Dim strConn As String
    Dim strSql As String

    strExcel = "C:\Data\ForecastInput.xlsx"
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strExcel & ";" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""

    Set cn = CreateObject("ADODB.Connection")
    cn.Open strConn

    strSql = "SELECT * FROM [Sheet1$] WHERE [Amount] IS NOT NULL"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSql, cn

    Do While Not rs.EOF
        CurrentDb.Execute "INSERT INTO tblForecast (Region, Amount, ForecastMonth) " & _
            "VALUES ('" & Replace(rs!Region & "", "'", "''") & "', " & _
            CDbl(rs!Amount) & ", #" & rs!ForecastMonth & "#)", dbFailOnError
        rs.MoveNext
    Loop

    rs.Close
    cn.Close
End Sub

Production code should use parameterized queries or recordset batch inserts-not string-concatenated SQL with Replace for quotes. The pattern above shows the connection string; we refactor to DAO.Recordset.AddNew or INSERT INTO … SELECT … FROM linked table for safety.

Need Access-Excel integration that actually runs in production?

We build linked reports, ADODB pipelines, and one-click export buttons for teams that outgrew copy-paste. Free scope review-reply within one hour.

Get Access-Excel integration help →

Method 6: ADODB from Excel VBA (pull from Access)

When Excel owns the report workbook and must pull fresh Access data on button click, run ADODB inside Excel. The workbook can live on SharePoint or a network folder; only the .accdb path and ACE provider must be valid.

Excel macro: open recordset against an .accdb file

' In Excel VBA (ThisWorkbook or standard module)
' Tools → References → Microsoft ActiveX Data Objects 6.1 Library

Public Sub PullAccessQueryIntoSheet()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strDb As String
    Dim strConn As String

    strDb = "\\Server\Share\Operations.accdb"
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"

    Set cn = New ADODB.Connection
    cn.Open strConn

    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM qryOpenOrders", cn, adOpenForwardOnly, adLockReadOnly

    With ThisWorkbook.Worksheets("Data")
        .Cells.Clear
        .Range("A2").CopyFromRecordset rs
    End With

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Query names in Access are exposed like tables to OLE DB. For parameterized queries, use a pass-through approach or call a saved query with WHERE replaced in VBA only after validating inputs. Never pass raw user text into SQL strings.

Method 7: CopyFromRecordset (fast bulk load)

Once you have an ADODB or DAO recordset, Excel’s Range.CopyFromRecordset writes thousands of rows in one shot-far faster than looping cells.

DAO in Access → Excel in one operation

Public Sub FastExportWithCopyFromRecordset()
    Dim rs As DAO.Recordset
    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    Set rs = CurrentDb.OpenRecordset("qryInventorySnapshot", dbOpenSnapshot)
    Set xl = New Excel.Application
    xl.Visible = True
    Set wb = xl.Workbooks.Add
    Set ws = wb.Worksheets(1)

    ws.Range("A1").CopyFromRecordset rs
    rs.Close

    ws.Rows(1).Font.Bold = True
    ws.Columns.AutoFit
End Sub

Comparison: which method should you use?

  • One-time ad-hoc

    Manual Export or Excel Get Data (Methods 1-2).

  • Analyst-driven refresh

    Excel Get Data with scheduled refresh if Power Query is enabled (Methods 2).

  • Button on Access form

    TransferSpreadsheet for simple dumps; ADODB + CopyFromRecordset for templates (Methods 4-7).

  • Excel template filled from Access

    ADODB from Excel VBA or Access writing to named ranges (Methods 5-6).

  • Import user Excel uploads

    ACE OLE DB to read sheets, then append to Access tables with validation (Method 5 import sample).

Troubleshooting real-world errors

  • Could not find installable ISAM

    ACE provider missing or wrong bitness. Install Microsoft Access Database Engine Redistributable matching Excel (64-bit vs 32-bit).

  • Not a valid file name

    Excel sheet reference must be [Sheet1$] including brackets and dollar sign for OLE DB.

  • Refresh works for one user only

    UNC vs mapped drive paths differ. Standardize \\server\share\file.accdb for all connections.

  • Operation must use an updateable query

    Linked Excel or multi-table queries are not updateable. Import to a staging table, validate, then append to production.

  • Slow exports

    Filter in Access queries, not in Excel. Index key fields in Access. Avoid SELECT * on wide tables.

  • #Num! or wrong dates from Excel

    Regional date formats and blank rows confuse ACE. Normalize dates in Excel and use IMEX=1; prefer import to staging with CDate validation in VBA.

Security and governance

  • Split the database

    Put the .accdb backend on a secured share; distribute a front-end only. Excel connections point to the backend path users are allowed to read.

  • Avoid embedded passwords in VBA

    Use Windows integrated security where possible, or store connection secrets in an encrypted table-not plain text in modules.

  • Audit write-back

    Log who imported which Excel file and row counts. See our audit log article for Access patterns.

  • Version control

    Export VBA modules to text files in source control; document which query feeds which Excel report.

Frequently asked questions

Can Excel edit Access data directly?

Only in limited linked-table scenarios. For production, treat Excel as input or output, not a second database UI-use forms in Access or controlled import macros.

Do I need Microsoft 365?

No. Get Data and ACE work in desktop Excel 2016 and later. Automation requires desktop Excel with VBA macros enabled (not Excel on the web).

Access vs Power Query?

Power Query in Excel overlaps with Get Data to Access. Use Power Query when transforming data in Excel; use Access VBA when the business process starts in Access (buttons, validations, multi-step workflows).

What about .mdb legacy files?

Same ACE provider with Jet compatibility. Migrate to .accdb when possible-better encryption and fewer driver surprises on Windows 11.

When to get expert help

Call a specialist when exports must land in branded templates, when multiple queries feed one dashboard with versioned file names, when Excel write-back needs validation and audit trails, or when ACE errors only appear on half your workstations. We implement these integrations weekly-usually as a button on an Access form plus a documented refresh path for analysts.

Related services: Excel data integration, Access VBA development, and API integration between Access and Excel. For larger scale, read migrating Access to SQL Server before Excel pulls millions of rows.