How to Connect Microsoft Access to Power BI

Still using Microsoft Access? You're not alone. Access remains a surprisingly robust tool for managing relational data, especially for small businesses, departmental projects, or legacy applications. But when it comes to visualizing that data and sharing insights, staring at rows and tables falls short. This is where you can unlock its real potential by connecting it to a modern business intelligence tool. This tutorial provides a complete step-by-step guide on how to connect your Microsoft Access database directly to Power BI to create dynamic, interactive reports and dashboards.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Connect Microsoft Access to Power BI?

Connecting your Access database to Power BI isn't just a technical exercise, it's a strategic move to breathe new life into your data. While Access is excellent at data entry and storage with a relational structure, Power BI is designed for data exploration, visualization, and sharing. Combining the two gives you the best of both worlds.

Here are the primary benefits:

• Transform Data into Decisions: Turn static tables into beautiful, interactive dashboards. A stacked bar chart showing sales by region is infinitely more insightful than a raw table of sales transactions. You can click, filter, and drill down to uncover trends you’d otherwise miss.
• Combine Data from Multiple Sources: Your business data probably doesn't live exclusively in Access. Power BI makes it simple to merge your Access data with information from Excel workbooks, SQL databases, SharePoint lists, or even web sources like Google Analytics, creating a single, unified view of your operations.
• Democratize Your Data Securely: Instead of emailing .accdb files (a security and version-control nightmare), you can publish a Power BI report to the cloud. You control who has access and can grant view-only permissions, so stakeholders can see the latest insights without needing Access installed or having the ability to alter the source data.
• Automate Your Reporting: Once connected, you can set up scheduled data refreshes. Your dashboards will automatically update daily or weekly with the latest information from your Access database, eliminating the tedious process of manually rerunning and distributing reports.

Prerequisites: What You’ll Need

Before you get started, make sure you have everything in place for a smooth connection process. It’s pretty straightforward, but a little prep work goes a long way.

• Power BI Desktop: You'll need the latest version of Power BI Desktop installed on your Windows machine. It's a free download directly from Microsoft. All the data connection and report design work happens here initially.
• Microsoft Access Database File: Have your Access file (.accdb or the older .mdb format) ready. Crucially, the file must be accessible from the computer where you are running Power BI Desktop. This can mean it's saved locally on your hard drive, on a network share, or in a mapped drive.
• (Optional) On-Premises Data Gateway: If your goal is to publish your report to the Power BI Service (the cloud component) and schedule automatic data refreshes, you will need the On-Premises Data Gateway. This is a small application you install on a computer within your network that acts as a secure bridge, allowing the Power BI cloud service to "reach" your local Access file to fetch new data. You don't need this just to build a report in Power BI Desktop.

A Quick Note on 32-bit vs. 64-bit

Power BI Desktop is typically a 64-bit application. Microsoft Office (which includes Access) can be either 32-bit or 64-bit. For Power BI to successfully communicate with Access, their "bitness" must align. If your Power BI Desktop is 64-bit but your Office/Access installation is 32-bit, you will likely get an error. The easiest solution is to download and install the free Microsoft Access Database Engine 2016 Redistributable in the 64-bit version. This lets Power BI talk to Access databases even if the full Access application isn't 64-bit.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step: Connecting Your Access Database to Power BI

With the prerequisites out of the way, you’re ready to connect your data. The process only takes a few clicks.

Step 1: Get Data in Power BI Desktop

First, launch Power BI Desktop. In the Home tab of the main ribbon, you’ll see a section called Data. The primary button here is Get Data.

Click on the "Get Data" button. This action opens a dropdown menu showcasing the most common data sources.

Step 2: Select the Access Database Connector

While Access is a popular source, it's not listed in the initial quick-access menu. At the bottom of the "Get Data" dropdown, click on More.... This opens the full Get Data window, which contains hundreds of data connectors.

In the new window, you can either scroll down the File category on the left or, more easily, use the search bar at the top left. Type "Access" into the search bar. The "Access database" connector will be highlighted. Select it and click the Connect button.

Step 3: Browse for and Select Your File

After clicking Connect, a standard Windows file browser dialog will appear. Navigate to the folder where your .accdb or .mdb file is saved. Select the file and click Open.

Security Tip: A pop-up may appear asking you to confirm that you trust the source. This is a standard security step when connecting to local files. Click "OK" to proceed.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Use the Navigator to Select Tables and Queries

Once Power BI establishes a connection, the Navigator window opens. This is a crucial step where you choose exactly what data you want to pull from your Access file into your Power BI report.

The Navigator will display all the available objects within your Access database, organized into two folders:

• Tables: These are the raw tables containing your data (e.g., tbl_Sales, tbl_Customers, tbl_Products). They will have a standard table icon.
• Queries: These are the queries you've built inside Access (e.g., qry_SalesByMonth, qry_CustomerList). They have a two-table icon.

You can import data from either. Often, choosing a pre-built Access Query is beneficial, as you might have already joined tables or created calculated fields within Access. When you click on a table or query name in the Navigator, a data preview will appear on the right side, giving you a quick look to confirm it's what you need. Select one or more objects by ticking the checkboxes next to their names.

Step 5: Decide Whether to 'Load' or 'Transform Data'

At the bottom right of the Navigator, you have two primary options:

• Load: This option loads the selected tables and queries directly into your Power BI data model as they are. This is a good choice if you are confident your data is perfectly clean, correctly formatted, and needs no adjustments.
• Transform Data: This is the more powerful and recommended option for almost all cases. Clicking "Transform Data" opens the Power Query Editor. This is the data preparation layer of Power BI, where you can clean, shape, and enrich your data before it even enters your report.

Always lean towards using "Transform Data." Even a quick review in the Power Query Editor can help you spot data type issues or columns that you don't need, which can make your report more efficient.

Data Preparation in the Power Query Editor

The Power Query Editor is a game-changer. It records every transformation step you make, so you don't have to repeat them every time you refresh. Think of it as a repeatable recipe for cleaning your data.

Even if you think your Access data is immaculate, here are a few common and valuable transformations you might perform:

• Remove Unnecessary Columns: Your Access table might have internal keys or legacy fields not relevant for analysis. Select the columns you don’t need, right-click, and choose "Remove." This makes your data model smaller and faster.
• Check Data Types: Power Query does a good job of guessing data types, but it's not foolproof. Quickly scan your columns and ensure numbers are properly formatted as "Whole Number" or "Decimal Number", dates are "Date", and text is "Text." Problems with data types are a common source of calculation errors later.
• Filter Rows: You might want to exclude certain records. For instance, you could filter out sales records where the OrderStatus is "Cancelled" or remove rows with blank customer IDs.
• Replace Values: Find and replace inconsistent data entries (e.g., changing "N/A" to "Not Applicable" or replacing null values with zero).

Once you are satisfied with the state of your data, go to the Home tab in the Power Query Editor ribbon and click Close & Apply. Your polished data will now load into your Power BI report, ready for you to start building visuals.

Managing and Refreshing Your Access Data

Connecting your data is only the first step. To make your report truly valuable, you need to keep it up-to-date.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Manually Refreshing in Power BI Desktop

When you are working in Power BI Desktop, any changes made to the underlying Access database are not reflected in your report until you refresh. The connection to Access is an "Import" connection, meaning Power BI takes a snapshot of your data. To get the new data, simply click the Refresh button in the Home ribbon. Power BI will rerun all the connection and transformation steps you defined and load in the latest data.

Automating Refreshes in the Power BI Service

Manually hitting refresh is fine for ad-hoc analysis, but the real power comes from automation. This is where the On-Premises Data Gateway becomes essential.

Here's the high-level workflow:

1. Install the Gateway: You must install the gateway on an always-on computer that has consistent network access to your Access .accdb file. This computer could be a local server or even a dedicated desktop machine.
2. Publish Your Report: From Power BI Desktop, publish your completed report to your Power BI workspace in the cloud.
3. Configure the Gateway Connection: Log into the Power BI Service (app.powerbi.com). Go to Settings &gt, Manage connections and gateways. Here, you'll configure your installed gateway, adding your Access file's path as a data source and providing the necessary credentials (usually Windows credentials).
4. Set the Refresh Schedule: Navigate to your dataset's settings in your workspace. Expand the "Gateway and cloud connections" section and map your dataset to the gateway data source you just created. Once mapped, the "Scheduled refresh" option will become available. You can toggle it on and set a schedule, such as refreshing every weekday at 9:00 AM.

With this setup, the Power BI Service will securely instruct the gateway to fetch fresh data from your Access file on schedule, ensuring your published reports are always current without any manual intervention.

Final Thoughts

Connecting Microsoft Access to Power BI is a powerful way to leverage your existing data infrastructure while adopting modern, dynamic reporting and analysis tools. By following the straightforward "Get Data" process, you can easily pull your tables and queries into Power BI, clean them in Power Query, and build insightful visualizations that are far more effective than static Access reports.

While mastering specific connectors like this for legacy systems is valuable, a major challenge in modern analytics is managing the explosion of cloud-based data sources. This is where we designed Graphed to simplify your entire analytics workflow. Instead of setting up individual connections and gateways for platforms like Google Analytics, Shopify, Salesforce, or Facebook Ads, we let you connect them all in one place and build custom, real-time dashboards using plain, conversational English - no more wrestling with complex BI tools or manually exporting CSVs from a dozen different apps ever again.