How to Connect Excel with MS Access — Every Way You Need to Know

If you've worked with MS Access and Excel for any decent amount of time, you've probably hit that moment where you thought — "I wish these two could just talk to each other." Well, they can. And they do it pretty well, actually.

In this post, I'm going to walk you through every meaningful way to connect Excel and Access — pushing data from Excel into Access, pulling data from Access into Excel, and most importantly, understanding ADODB, the engine that powers most of this under the hood. No fluff, just the real stuff with working code you can use today.

Why Connect Excel and Access at All?

Excel is where people live. It's comfortable, visual, and flexible. Access is where data should live — structured, relational, and queryable. The problem is they're two separate applications, and most people either copy-paste between them manually (painful) or don't know there's a better way.

Connecting them properly means you can build dashboards in Excel that pull live data from Access, create data entry forms in Excel that write directly into your Access tables, and automate the whole thing with VBA so nobody has to touch a file path or click an import wizard ever again.

Method 1 — Microsoft Query (No VBA Required)

This is the most beginner-friendly way to pull data from Access into Excel. No code involved.

Go to the Data tab in Excel, click Get Data, then From Database, then From Microsoft Access Database. Browse to your .accdb file, pick your table or query, and load it into a sheet or the data model.

Excel stores the connection and you can refresh it anytime. It's clean, it's quick, and it works fine for read-only reporting scenarios. The downside is you have no real control — you can't filter dynamically based on a cell value, and you definitely can't write data back to Access this way.

Method 2 — Power Query (Modern and Powerful)

Power Query is the upgraded version of Microsoft Query. It's available in Excel 2016 and later, and it handles Access connections very smoothly.

Go to Data → Get Data → From Database → From Microsoft Access Database. Once connected, Power Query opens its editor where you can filter rows, rename columns, join tables, and transform your data before it ever lands on the sheet.

The real power here is the M code running behind the scenes. Your connection is repeatable and refreshable. Again though — this is a one-way street. Power Query reads data; it doesn't write back.

Method 3 — DoCmd.TransferSpreadsheet in Access VBA

If you're writing code inside Access itself, this is the simplest way to push or pull an entire Excel sheet.

To import from Excel into Access:

To export from Access to Excel:

This is fast and straightforward but you're running it from Access. If your automation lives in Excel, you need something else — which brings us to the main event.

Method 4 — ADODB Connection from Excel VBA

This is the method you'll use 90% of the time when building serious Excel-Access automation. ADODB (ActiveX Data Objects DataBase) lets you open a connection to Access directly from Excel VBA, run SQL queries, read data back, and write records — all without opening Access.

Before anything else, you need to enable the reference. In the VBA editor, go to Tools → References and check Microsoft ActiveX Data Objects 2.x Library (use the highest version available).

Understanding ADODB — What's Really Happening Behind the Scenes

This is where most tutorials skip over the good stuff. Let's actually understand what ADODB is and how it works.

ADODB is a COM-based data access layer built by Microsoft. When you write ADODB code in VBA, you're not talking directly to your Access file. Instead, you're talking to a middle layer called OLE DB (Object Linking and Embedding Database). OLE DB then talks to a provider — in this case, the Microsoft ACE OLEDB provider — which is the actual driver that knows how to read and write .accdb and .mdb files.

Think of it like this. Your VBA code is the customer. ADODB is the waiter. OLE DB is the kitchen order system. The ACE OLEDB provider is the chef who actually knows the recipe. The Access file is the pantry.

When you open a connection, ADODB hands your connection string to OLE DB, which loads the ACE OLEDB provider (Microsoft.ACE.OLEDB.12.0) into memory. The provider opens the Access file, sets up a file lock, and maintains a session. From that point, every SQL command you send goes through this chain and comes back with results.

The ADODB object model has four key objects you need to understand:

Connection — This represents the open session with the database. It holds the connection string, manages transactions, and is the gateway for everything else. Opening and closing this properly is critical. A connection left open will lock your Access file.

Recordset — This is the result of a SELECT query. Think of it as a temporary in-memory table that holds your rows and columns. You move through it row by row using .MoveNext and check if you've hit the end with .EOF (End Of File).

Command — This lets you run parameterized queries and stored procedures. It's more powerful than running SQL directly through the Connection object because it lets you pass parameters safely without building SQL strings by hand.

Parameter — Used with the Command object to pass values into queries. This is the right way to handle user input because it prevents SQL injection and handles data types correctly.

Pulling Data from Access into Excel Using ADODB

Here is a complete working example:

vba

The CopyFromRecordset method is your best friend here. It dumps the entire recordset onto the sheet starting from a given cell — fast and clean.

Inserting Data from Excel into Access Using ADODB

Now the other direction — taking values from your Excel sheet and writing them into an Access table.

vba

A quick note on dates — Access uses the # symbol as a date delimiter in SQL, not quotes. And the date format must be mm/dd/yyyy regardless of your regional settings. This trips up a lot of people.

The Right Way — Using Parameterized Queries

Building SQL strings by concatenating cell values works but it's fragile. A customer name with an apostrophe (like O'Brien) will break your INSERT. The proper approach uses parameterized queries via the Command object:

vba

Each ? in the CommandText maps to a parameter in the order they're appended. The provider handles all the quoting, escaping, and type conversion for you.

Updating and Deleting Records

Inserting isn't the only thing you'll need. Here's how to update an existing record:

vba

And to delete:

vba

These run just like INSERT — pass them through conn.Execute and they run immediately.

Using Transactions for Bulk Operations

If you're inserting hundreds of rows and something fails halfway through, you don't want half your data in Access and half missing. Transactions solve this:

vba

Either everything commits or nothing does. This is especially important for financial or inventory data where partial writes can cause serious problems.

Common Connection String Variations

For an Access 2007 or later .accdb file:

For an older .mdb file:

For a password-protected Access file:

A Few Things That Will Save You Hours of Debugging

Always close your Recordset before closing your Connection. Always set both to Nothing afterward. If you don't, Access keeps the file locked and you'll get a "file already in use" error next time you run the macro.

If you get a "Provider cannot be found" error, it means the ACE OLEDB driver isn't installed or your Excel is 64-bit but the driver is 32-bit. Install the Microsoft Access Database Engine 2016 Redistributable from Microsoft's website and make sure the bitness matches your Office installation.

If your date inserts are coming through wrong, use the Format function explicitly and always use the mm/dd/yyyy pattern in the SQL string regardless of what your Windows regional settings say.

Summary

Excel and Access work best when they're connected, not isolated. For simple one-time imports and exports, DoCmd.TransferSpreadsheet from Access VBA or Power Query from Excel gets the job done without any code. For automated, repeatable, two-way data workflows, ADODB is the right tool.

ADODB works by sitting between your VBA code and your Access file, routing commands through the OLE DB layer and the ACE OLEDB provider. The four objects to master are Connection (opens the session), Recordset (holds query results), Command (runs parameterized SQL), and Parameter (safely passes values into queries).

To pull data from Access into Excel, open a connection, open a recordset with your SELECT query, and use CopyFromRecordset to dump it onto the sheet. To push data from Excel into Access, loop through your rows and execute INSERT statements — preferably parameterized ones using the Command object. Use transactions any time you're doing bulk writes so you never end up with partial data.

Once you've got this wired up, a lot of things that used to feel manual and fragile — monthly reports, data entry forms, sync routines — start feeling automatic and solid. That's exactly where you want to be.