An ODBC connection is the standard way to read Sage 50 Accounts data into Power BI or Excel. Once it’s set up, you can build reports directly on your live accounting data instead of exporting to spreadsheets every month.

This guide covers the connection itself. If your goal is a Power BI report that refreshes automatically in the Power BI Service, you’ll need the Microsoft On-premises Data Gateway as well — that’s covered separately at the end.

What the connection does, and what it doesn’t

It’s read-only. The Sage 50 ODBC driver cannot write to your data. Nothing you do in Power BI or Excel can change, delete or corrupt a transaction in Sage. This is the first question most finance teams ask, and the answer is reassuring: you are pointing a reader at your accounting data, not an editor.

It doesn’t slow Sage down. In our experience across many Sage 50 installations over several years, we have not seen an ODBC connection cause a noticeable performance impact on Sage 50 Accounts for the people using it day to day.

It gives you everything. A single ODBC connection exposes over 100 tables — enough to build a full profit and loss, balance sheet, aged debt or stock report, drillable down to individual transaction lines.

Before you start

  • You have direct network access to the Sage 50 Accounts data files that hold your company data. In practice this means Sage 50 Accounts is installed on the machine and you can open the target company in Sage.
  • The ODBC driver is installed for the version of Sage 50 Accounts you’re running. This happens automatically when you install Sage, but the drivers can also be downloaded from Sage.
  • You have valid Sage 50 login credentials for the company you want to connect to.

Step 1 — Open the ODBC Data Source Administrator

Open the Windows Start menu and search for ODBC. You’ll see two entries: ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit).

Use the 64-bit version. Both of the things you’re likely to do next are 64-bit only: Power BI Desktop is now shipped exclusively as a 64-bit application, and so is the Microsoft On-premises Data Gateway you’ll need if you want scheduled refreshes.

The one exception worth knowing: if you’re connecting from a 32-bit installation of Excel and nothing else, you’ll need the 32-bit driver instead, because the driver architecture has to match the application consuming it. If you’re not sure which Excel you have, check File → Account → About Excel.

Step 2 — Create a System DSN

A DSN (Data Source Name) is the named connection that your reports will point at.

You have two options: a User DSN, visible only to your own Windows account, or a System DSN, visible to everyone on the machine. Create a System DSN. Beyond being more practical in a shared environment, the On-premises Data Gateway can only see System DSNs — so a User DSN will quietly block you later when you try to set up scheduled refresh.

  1. Go to the System DSN tab
  2. Click Add
  3. Choose the Sage 50 Accounts driver from the list — it will be named something like “Sage Line 50 v30”
  4. Match the driver version to your installed Sage 50 version. If you’re on Sage 50 v28, select the v28 driver
  5. Click Finish

Step 3 — Name your DSN

The name you choose here is what every report will reference, so it’s worth a moment’s thought.

  • Include “Sage 50” so the connection is easy to identify when you’re browsing data sources
  • Include the company name. Each DSN connects to a single Sage company, so multi-company setups need one DSN per company
  • Leave the Sage version number out. Your reports reference the DSN by name — if the name contains “v29” you’ll be editing every report the day you upgrade Sage
  • Avoid spaces, which reduces the chance of typos when the name appears in query code

For example: Sage50_CompanyName

Step 4 — Point the DSN at your Sage data

  1. Open Sage 50 Accounts
  2. Go to Help → About
  3. Under Program Details, find the Data Directory path
  4. Click the link to open the folder in File Explorer
  5. Go into the ACCDATA subfolder
  6. Copy the full path from the address bar
  7. Paste it into the Data Path field in the DSN configuration window

Click OK. Your new data source should now appear in the System DSN list.

Step 5 — Test the connection

  1. Open Power BI Desktop or Excel
  2. Go to Get Data → From Other Sources → From ODBC
  3. Select your new DSN
  4. Click OK
  5. Enter your Sage 50 credentials when prompted
  6. If it works, you’ll see the full list of Sage tables available to import

Worth doing even if you’re confident, and worth doing on the machine where the DSN lives. If something goes wrong later with a gateway or a scheduled refresh, knowing the DSN itself is sound narrows the problem down considerably.

If you’re on Sage 50 Cloud

ODBC works the same way with Sage 50 Cloud, with one caveat that matters for reporting.

Sage Remote Data Access syncs changes between users across the network, and the ODBC connection reads the local data files. So the connection returns entries that have completed their sync — anything still in flight won’t be visible yet. In practice this means a report refreshed immediately after someone posts a batch of transactions may not include them. It’s rarely a problem for month-end reporting, but it’s worth knowing before you go looking for a transaction that appears in Sage and not in your report.

Automating refresh in the Power BI Service

Everything above gets you a working connection in Power BI Desktop. To publish a report and have it refresh on a schedule, the Power BI Service needs a route back to your on-premises Sage data, which means installing and configuring the Microsoft On-premises Data Gateway on the machine that holds the DSN.

There are a few points in that process where it’s easy to get stuck — the gateway service needs explicit Windows permission to read the ACCDATA folder, and the connection is defined differently in the Service than it is in Desktop.

[Link to gateway guide]

Where to go from here

Getting the data out is the easy part. The work that follows — building a data model that handles fiscal periods properly, getting a P&L that ties to Sage, making it something the wider management team will actually open — is where most finance teams find the time goes.

If you’d rather build it yourself, our Power BI finance templates and training for accountants are at accountinginsights.pro.

If you’d rather not, that’s what we do. Book a 30-minute call and we’ll tell you honestly whether we’re the right fit.