Connecting to a Sage 50 Accounts Database from VBA

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.

The Code

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

  1. 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.
  2. Once the ODBC driver is installed on the PC, navigate to the ODBC Data Source Administrator via Control Panel > Administrative Tools.
  3. Click the tab “System DSN”, and then click the “Add…” button.
  4. 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

VBA References window

  1. 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.
  2. 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.
  3. 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.

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

Back to Top