<img src="https://letcheck.b-cdn.net/3081/clxg00vgd00nk769naudifqzt.jpg" style="width: 100%; height: auto; cursor: pointer;" draggable="true">How to create a Sage 50 Accounts ODBC Excel connection with your <a target="_blank" rel="noopener noreferrer nofollow" href="https://accountinginsights.co.uk/connect-power-bi-sage-50-accounts">Accounting Insights connector for Sage 50 Accounts.</a>&nbsp; This has a number of advantages over using the standard facilities offered by Sage 50 Accounts:<ol><li>Setting up the connection is much easier (this is a matter of opinion of course, but why not try it?)</li><li>Loading and refreshing your data in Excel is much faster</li><li>Your Excel files are lighter – the connector excludes completed transactions that are more than 25 months old</li></ol>There are two scenarios covered in this article: the first where you have the Sage 50 Accounts connector and Excel running on the same machine, and the second where the two are on separate machines (for example you have the connector running on your Sage server and Excel running on a local machine).<h2 style="text-align: start" id="99380ead-5b31-4fa2-91d3-3e702274d5fe" data-toc-id="99380ead-5b31-4fa2-91d3-3e702274d5fe">Step 1 – Install PowerQuery for Excel</h2>Before you do either option, the first thing that you will need to do is to make sure that you have the PowerQuery for Excel add-in installed.&nbsp; Open up Excel, and if it displays a “Data” tab with the option for a “New Query”, then you already have PowerQuery for Excel installed.<img src="https://letcheck.b-cdn.net/3081/clxg02vns00oe2k8z5j0k7cl6.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true">If you don’t already have PowerQuery for Excel, you can download it from this <a target="_blank" rel="noopener noreferrer nofollow" href="https://www.microsoft.com/en-ie/download/details.aspx?id=39379">Microsoft download page</a>.<h2 style="text-align: start" id="99c2b450-452a-4054-b07f-01f3f5669124" data-toc-id="99c2b450-452a-4054-b07f-01f3f5669124">Step 2 – If your Sage 50 Accounts connector and Excel are on the same machine</h2>This is a very simple scenario.&nbsp; In the background your Accounting Insights connector for Sage 50 Accounts has created a DSN (Data Source Name) for each of your connected Sage 50 companies.&nbsp; To connect to a particular company, all you need to do is select New Query &gt; From Other Data Sources &gt; From ODBC, as shown in the image below.<img src="https://letcheck.b-cdn.net/3081/clxg050de00ou449nd8xg6xcv.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true">You will be prompted to select the DSN that you would like to connect to:<img src="https://letcheck.b-cdn.net/3081/clxg05v3w00pa548z1tk5efbr.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true"> Simply select your company DSN to connect.&nbsp; Avoid the temptation to connect to SageLine50v26 or SQLite3 Datasource.&nbsp; These are not the DSN’s created by your connector.You will be prompted to enter a username and password:<img src="https://letcheck.b-cdn.net/3081/clxg07a3z00nm599n0qa53r38.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true">Select “Default or Custom”, don’t enter anything into the connection string parameters, and select “Connect”.<img src="https://letcheck.b-cdn.net/3081/clxg087jy00pw8f8zc717078j.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true">Excel PowerQuery will then prompt you to select which table you would like to load into Excel.<img src="https://letcheck.b-cdn.net/3081/clxg0928a00ps6h9n8gld7brc.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true">You can see a full list of tables available <a target="_blank" rel="noopener noreferrer nofollow" href="https://accountinginsights.co.uk/sage-50-accounts-database/">in this article</a>.Unlike in Power BI, where you can select multiple tables at once, in Excel PowerQuery you need to do this one at a time.&nbsp; Each table loaded will create a table in its own tab in your workbook.Once you have established all of your connections, though, you can simply refresh them all using the “Refresh All” button in the Data tab.<img src="https://letcheck.b-cdn.net/3081/clxg0bh0v00ol599na1q852e4.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true"><h2 style="text-align: start" id="e3a36855-ef8d-4829-960e-fd3b56269a4f" data-toc-id="e3a36855-ef8d-4829-960e-fd3b56269a4f">Step 2 – If your Sage 50 Accounts connector and Excel are on the different machines</h2>The DSN that the Sage 50 Accounts connector creates is local to the machine on which the connector is installed.&nbsp; We, therefore, need to do something else if we are using Excel on a different machine.&nbsp; We need to define a new DSN on the machine that has Excel.&nbsp; This is straightforward enough, but a little less intuitive.You will need to do the following:<ol><li>Install a SQLite 3 database driver on your local machine. This is available as a <a target="_blank" rel="noopener noreferrer nofollow" href="http://www.ch-werner.de/sqliteodbc/">free download</a>.&nbsp; Choose sqlliteodbc.exe if you are running a 32-bit machine or sqliteodbc_w64.exe if you are running a 64-bit machine.&nbsp; Most new machines these days are 64-bit, but if you are not sure then you can check on your PC by going to Settings &gt; About.</li><li>Open the ODBC Data Source Administrator app on your computer. You can find this by searching for “ODBC” as shown below:</li></ol><img src="https://letcheck.b-cdn.net/3081/clxg0decp00ru7q8zerk8f47d.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true"><ol start="3"><li>Add a new DSN on your computer for your Sage 50 Accounts database that the connector created.<img src="https://letcheck.b-cdn.net/3081/clxg0g8g900s24t8z13uobbuk.png" style="width: 100%; height: auto; cursor: pointer;" draggable="true"> 4. Configure the settings of the new DSN For the Driver, select “SQLite3 ODBC Driver” from the list of options. You then need to provide two pieces of information:&nbsp;&nbsp;<ul><li>Data source name (DSN). Give it whatever name you want.</li><li>Database Name: Here you need to put in the complete path and name of the database that the connector has created.&nbsp; The name will be a file of type .db. You can find the path on the server if you open the connector and select the Companies tab.&nbsp; By default it is: C:\Users\******\AppData\Roaming\Accounting Insights Ltd\Sage 50 Accounts Connector\AppData\.&nbsp; If you use the option to browse from the ODBC Data Source Administrator, then you should be able to pick up the correct path. </li></ul>5. Now you can simply connect from Excel, using your newly-created DSN, following the steps above (as if you had the connector and Excel on the same machine).</li></ol>

Sage 50 Accounts ODBC Excel connection

Step 2 – If your Sage 50 Accounts connector and Excel are on the same machine

Step 2 – If your Sage 50 Accounts connector and Excel are on the different machines

Learn how to grow and scale your online course business using our online course platform. EzyCourse blog is designed to teach you how to create and sell online courses.

Sage 50 Accounts ODBC Excel connection

Sage 50 Accounts ODBC Excel connection

Related Posts

Power BI Profit and Loss Template

Power BI Accounts Receivable Template to drive down debtor days