So you’ve found your way into the database behind Sage 50 Accounts, it’s time to have a poke around
As promised in my previous post, Connecting to a Sage 50 Accounts Database from VBA, I described how to go about setting up a connection using VBA and ODBC to extract data from a Sage 50 Accounts database.
Unless you are a registered Sage developer the inner workings of their databases are kept shrouded in mystery (unless you’re using Sage 200 Accounts that is, where you can just quite simply open up the database in SQL Server Management Studio and mess about with it to your heart’s content). Sage 50 Accounts uses a proprietary database format, passed down through the ages largely unchanged, not widely suited to today’s multi-user network environments.
Even without a Sage Developer subscription it is possible to discover and explore the secrets within the Sage 50 Accounts database; it just takes a little bit of ingenuity, and a copy of Microsoft Access. I warn you now that I will not be going into a detailed description of the purpose of each table, the most commonly used ones are self-explanatory anyway (e.g.
Create an ODBC connection to your Sage 50 Accounts database
We won’t go through this again, as I explained it in my previous post.
Open the ODBC connection in Microsoft Access
- Open Microsoft Access, create a blank database and save it somewhere.
- Due to the changes made to the Microsoft Office suite over the previous years (I still prefer 2003) the option you are looking for may be in a different place, but essentially you should locate and select “Import External Data” (that’ll be “Get External Data -> Import…” from the File menu in 2003).
- Select “ODBC” as the type of connection you wish to import from.
- An ODBC data source selection window will appear. Select the connection to your Sage 50 Accounts database.
Import the Sage 50 Accounts database tables
- After entering your credentials you’ll be greeted with a new window listing all the tables available for import from Sage 50 Accounts into Microsoft Access. Highlight the tables you wish to examine and click OK.
- It may take several minutes to import the data, depending on the size and number of tables you have selected to import.
- Once completed you will see new tables within your Microsoft Access database, copies of the data from your Sage 50 Accounts.
You can open and view the records inside these tables, and explore the design of these tables exactly as you would in any other Microsoft Access database. With some deductive reasoning you should be able to determine what fields you need to provide to your SQL queries to extract data directly from the Sage 50 Accounts database, or you could create a sporadic dump of the data into Microsoft Access (as we have done here) and write queries to select from this non-live data.