Sage 50 Accounts uses a proprietary database. Here’s how to get inside it.
Because of the rock-and-roll nature of my life, one of the most common questions I’m asked is “How do you connect to a Sage 50 Accounts database through VBA?”
Usually this is required for the construction of heavily customised Excel reports, or maybe an Access data store used as an adapter for some other reporting tool. I’ve used it to pull out Sales Order volumes in order to populate production schedules, amongst many other things.
Whatever the purpose, the process is actually quite simple. Let’s launch into it.
Public Function GetSage50AccountsData(prmSqlQuery As String) As ADODB.Recordset Dim sageDatabase As ADODB.Connection Dim sageRecordSet As ADODB.Recordset Set sageDatabase = New ADODB.Connection Set sageRecordSet = New ADODB.Recordset sageDatabase.Provider = "MSDASQL" sageDatabase.ConnectionString = "DSN=OdbcDatasourceName;User ID='SageUsername';Password='SageUserPassword';" sageDatabase.Open sageRecordSet.ActiveConnection = sageDatabase sageRecordSet.Open prmSqlQuery Set GetSage50AccountsData = sageRecordSet 'Clean up Set sageDatabase = Nothing Set sageRecordSet = Nothing End Function
Before using the code, first create an ODBC connection
- Each version of Sage 50 Accounts comes with an ODBC driver installer, which you can copy from the installation disc and install on any PC that needs to connect to a centralised Sage 50 Accounts database.
- Once the ODBC driver is installed on the PC, navigate to the ODBC Data Source Administrator via Control Panel > Administrative Tools.
- Click the tab “System DSN”, and then click the “Add…” button.
- Adding a Sage 50 Accounts ODBC connection is pretty straightforward; give it a name and then enter the path to the ACCDATA folder for the Sage 50 Accounts company database to which you’re looking to connect.
Then get the code into your VBA
- On the menu at the top of the VBA IDE go to Tools > References. A window should appear, containing a long list of various goodies you can attach to your VBA project. We’re after just one; “Microsoft ActiveX Data Objects 2.8 Library”, which allows us to use the ADODB connectivity needed by the above code. Tick the box next to this and ‘OK’ out of this window.
- Add the above function into your VBA code somewhere. I recommend placing it in a separate module file so that it is nicely segregated, and then you can accompany it with any other Sage 50 Accounts database utility functions that you might want to write.
- To use the code, you need to create a recordset to store the returned data, and then call the above procedure with a SQL query (remembering that Sage 50 Accounts uses a bastardised version of SQL, so not all valid SQL syntax is valid within Sage 50 Accounts).
Here’s an example procedure that gets all product reference codes from the database, then displays them in the “Immediate” window.
Private Sub InterrogateSage() Dim sqlQuery As String Dim sageRecordset As ADODB.Recordset sqlQuery = "SELECT stock_code FROM stock ORDER BY stock_code;" Set sageRecordset = GetSage50AccountsData(sqlQuery) 'Display the products Do Until (sageRecordset.EOF = True) Debug.write("Reference is: " & sageRecordset.Fields("stock_code").Value) sageRecordset.MoveNext Loop 'Clean up sageRecordset.Close Set sageRecordset = Nothing End Sub
That’s the basics of it. In my next post I’ll be going into how to discover more about the exact structure of a Sage 50 Accounts database, so you’ll be able to find what fields to use to build your SQL statements. Until then I recommend you try to get the above code singing, by which time you’ll be ready to begin exploring the inner workings of the database.
For those working with Sage 200 Accounts, connecting to the database from VBA is pretty similar to this. The main difference is the use of the SQL Server drivers during ODBC setup, but other than that it’s all pretty much the same.