SQL Connector Installation for Sage 50C Accounting (UK/NI/NZ/AU)
The SQL Connector from Redware Research Limited will synchronise your Sage 50C Accounting data with SQL Server or MySQL relational database - http://www.redware.com/sql-connector
00:27 Introduction
0:55 Register your Software
1:40 Install the Connector
2:00 Fix License
2:30 Create SQL Server Database
3:20 Create Schema
3:40 Connect to Sage 50C
4:12 Specify Sage 50C - SQL Server Connection
4:40 Create Datafiles
5:10 See SQL Tables
5:40 TXT files and Power BI
7:00 Conclusion
If you have always wanted to connect your Sage 50 Accounting Database to a SQL relational database, I am going to show you how to do it with the Redware SQL Connector.
Now there are a few things we have to do. I am going to show you how to connect Sage 50C to SQL Server. First. we need to download and register the Connector software/ Then we must create the SQL Server database and the schema. Finally, we can run the Connector software to extract the data out of Sage 50Cinto SQL Server. Once all the data is extracted, we can also export to Power BI.
We need to install and register the connector from the Redware website. We see the sign up button at the very top left of the www.redware.com home page and we register with our email address at password and choose the product we want. We are going to choose SQL Connector for Sage 50, but it is the same process for all products.
After registration we can see a page with out selected product and the important thing here is to copy, with Control-C, the serial number as we are going to need that later. We can install the connector from here by pressing the DOWNLOAD CONNECTOR button which will pop up a Microsoft Click Once installation.
Click Once will download a setup executable fil into the downloads area of our browser and we can click double click on this to install the application. We may be asked some operational questions along the way, and it will take a minute or so to install and then the software will appear with a shortcut on the desktop. The program is properly installed and also available from the Window menu and can be removed with the Add/Install programs option in the control panel.
Once the software is installed and running, we need to register the license by clicking on the SETUP tab and selecting the LICENSE page. Press the ADD button and then we can paste in the license number with Control-V and then press SAVE. Once the license is registered, additional tabs appear for the SAGE and SQL settings.
We are ready to create our database. We can use either SQL Server or MySQL. In this case, we need to log into the SQL Management Console and make a new database by selecting the DATABASES option and selecting NEW DATABASE.
In order to connect the software, we need a connection string which is normally in the following format:
server=serveripaddress;database=databasename;uid=username;pwd=password.
In this case, on my local machine, we can use a connection string for the local database that uses my current user credentials to log in to the SQL Server:
server=(local);database=connector17;trusted_connction=true;
We can add the connection to SQL Server into the Connector from the SETUP=SQL tab. Choose SQL SERVER as the database type and paste in the connection string and press TEST and SAVE. It the connection is good, then press EDIT and CREATEE SCHEMA for the Connector to create the tables. You can look using the SQL MANAGEMENT CONSOLE and see the newly created tables. See, for example, the CUSTOMER table and so on, which will be filled in a minute with the data from Sage 50C.
We can connect to Sage 50C from the SETUP-SAGE tab. We need to press EDIT and it will default to the default DEMODATA folder location here. You need to put in your real accounting folder and remember to put the ACCDATA suffix on the end and enter the correct username and password. SAVE and TEST and now the Sage connection is OK.
We just need to connect our Sage 50C with our SQL Server, which we do from the POWER BI page. Select the SQL Connection and edit by pressing the MODIFY button to bring up a tab for the DEFAULT Sage 50C Connection. On the Sage 50C Connection tab, press EDIT and choose the SQL Server connection to join the two together.
Now if we go back to our Power BI tab, we can select the Sage 50C Connection press the CREATE DATAFILES button to create the data inside the SQL database.
We can now return to the SQL database management system and have a look at the data. We can see that the data has been populated and we can do anything we like with it.
The DATA FOLDER has twelve Excel spreadsheets representing all of your accounting data, which can be used to populate our Power BI report.
Please see the automation video to see how you can get it to run automatically every day and also refresh Power BI if you wanted to.
There is also a separate video showing how to connect to MySQL. If you have a different SQL database, then please get in touch for support.