Sage 50 Accounts ODBC Excel connection

How to create a Sage 50 Accounts ODBC Excel connection with your Accounting Insights connector for Sage 50 Accounts.  This has a number of advantages over using the standard facilities offered by Sage 50 Accounts:

1. Setting up the connection is much easier (this is a matter of opinion of course, but why not try it?)
2. Loading and refreshing your data in Excel is much faster
3. Your Excel files are lighter – the connector excludes completed transactions that are more than 25 months old

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).

Step 1 – Install PowerQuery for Excel

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.  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.

If you don’t already have PowerQuery for Excel, you can download it from this Microsoft download page.

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

This is a very simple scenario.  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.  To connect to a particular company, all you need to do is select New Query > From Other Data Sources > From ODBC, as shown in the image below.

You will be prompted to select the DSN that you would like to connect to:

Simply select your company DSN to connect.  Avoid the temptation to connect to SageLine50v26 or SQLite3 Datasource.  These are not the DSN’s created by your connector.

You will be prompted to enter a username and password:

Select “Default or Custom”, don’t enter anything into the connection string parameters, and select “Connect”.

Excel PowerQuery will then prompt you to select which table you would like to load into Excel.

You can see a full list of tables available in this article.

Unlike in Power BI, where you can select multiple tables at once, in Excel PowerQuery you need to do this one at a time.  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.

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

The DSN that the Sage 50 Accounts connector creates is local to the machine on which the connector is installed.  We, therefore, need to do something else if we are using Excel on a different machine.  We need to define a new DSN on the machine that has Excel.  This is straightforward enough, but a little less intuitive.

You will need to do the following:

1. Install a SQLite 3 database driver on your local machine. This is available as a free download.  Choose sqlliteodbc.exe if you are running a 32-bit machine or sqliteodbc_w64.exe if you are running a 64-bit machine.  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 > About.
2. Open the ODBC Data Source Administrator app on your computer. You can find this by searching for “ODBC” as shown below:
   
   
   
   
3. Add a new DSN on your computer for your Sage 50 Accounts database that the connector created.
   
   
   
   
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:  
   • Data source name (DSN). Give it whatever name you want.
   • Database Name: Here you need to put in the complete path and name of the database that the connector has created.  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.  By default it is:
     C:\Users\******\AppData\Roaming\Accounting Insights Ltd\Sage 50 Accounts Connector\AppData\.  If you use the option to browse from the ODBC Data Source Administrator, then you should be able to pick up the correct path.
     
     
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).