In this video, I'm going to show you how to create an ODBC connection to Sage 50 accounts that you
can then use within Power BI or Excel to extract your data. And to do that we need firstly we need
to make sure that we're on the same machine or with direct access to your Sage 50 Accounts
data…And then we go to the ODBC data source manager. And we do that by searching our windows
applications and we just type in ODBC, and we are presented with two choices; 64 bit or 32 bit.
And it is the 64 bit one that we want because Sage 50 Accounts is a 64 bit application…And
then there are two choices. Or one choice of two things. and that's to create a user data
source name or a system data source name. And the difference between the two is if you create
a user data source name only you will be able to use it. Whereas if you create a system DSN,
then other people will also be able to use that connection that you've established. So
today we're going to create a system DSN. And we just choose add…And you're prompted to select the
driver for which we want to set up a data source. And since we want to connect to Sage 50 Accounts,
we need to select the Sage 50 Accounts driver. Now these drivers will be installed when you installed
Sage 50 automatically. And if I scroll down, I can see I've got a version 26 driver version
27 and version 28. It's important that we get the right version for the version of Sage 50
that we're using. I'm using version 28 So I select the version 28 driver and finish…And
then just two more things we need to do. Firstly, we need to give our data source a name.
And I give you three tips on that. The first tip is; Let's include Sage 50 in the title, so that,
when we're browsing our data source names, we know that this is a Sage 50 connection.
And the second tip I would give is to identify the company we're connecting to within Sage 50.
Because Sage 50 is a multi-company system. And each data source name will connect to
an individual company, so ff we have multiple companies we will need a DSN for each company
that we want to connect to. And the third tip that I would give is not to include the
version of Sage 50 within the name of the data source. And that's for a very simple
reason is that you're going to use this data source name within your Power BI reports.
And if you upgrade Sage, you don't want to have to update all your Power BI reports…We're
going to give our data source and name of Sage 50-AI…AI
for Accounting Insights because that's the company that we want to connect to. And then finally we
need to identify the data path. Now, I don't know what the data path is. But it's easy to find,
so if we open up Sage. Which I have open here. And if we go to Help -> About…Under
Program Details we can see the Data Directory. And this is where our data is stored So…if
we click on that link...That'll open up File Explorer. Now we're nearly there.
But actually, what we want to do is browse to the Accdata folder. So, if we…Double click on
the Accdata folder. Here are all our data files, and if we just select in the ribbon at the top,
now we can copy that…and paste that as our data path for our new data source name…And
that's it created so we can scroll down…and we here we can see, our Sage 50 AI
Data Source Name. And we can very quickly check it…So if I open up an Excel…And
we go to Data -> Get data…From Microsoft query…And we scroll down to the Data Source Name that we
created Sage 50-AI…and we click Okay. Now because this is trying to connect to a particular company,
we need valid credentials to connect to that company. It's not just anybody can access it.
So…type in our credentials. And there we have it. All our tables from Sage 50
that we can then load into Excel and we can do it the same in power BI as well…