Get Xero or MYOB data into Microsoft Access using linked tables to Microsoft Excel

OdataLink provides a simple method to download data from MYOB or Xero. This is done using the OData Feeds technology.

This technology is native within Power BI and Excel using Power Query. Unfortunately, Microsoft Access does not include Power Query or OData Feed support. Luckily, there are a few work arounds.

One approach involves using Microsoft Excel and Power Query to download and shape the data. Using this approach, each table will appear on its own sheet within an Excel Workbook. You can then use Linked Tables native to Microsoft Access to consume this data. Once setup, you can simply refresh the Excel Workbook and save it to get access to the latest data.

This article will explain everything you need to know to get Xero or MYOB data into Microsoft Access using linked tables to Microsoft Excel workbooks using OdataLink.

Setup your OdataLink account

First, you will need to ensure you have an OdataLink account setup. The following articles will assist.

Get MYOB data into Excel
Get Xero data into Excel

Choose the right format of Microsoft Excel Workbooks

Once your OdataLink account is setup, you are ready to start pulling data into Excel.

Before you do, however, it is important to choose the right format for your Microsoft Excel Workbook. The format you choose is dependent on which Microsoft Access database you are using.

Microsoft Access databases come in two different formats:

  • the newer ACCDB format
  • the older MDB format

To find which version of Microsoft Access databases you use, simply check the file extension of your database. The easiest way to do this is using the following steps.

  1. Right-click on your file and choose Properties.
  2. Go to the General Tab.
  3. Check the Type of File entry.

Once you know the format of your Microsoft Access Database, you can choose the correct format for your Microsoft Excel workbook.

If you are using the newer ACCDB format, you can choose any Microsoft Excel Workbook format. However, we strongly recommend you use one of the following:

  • Excel Workbook (*.xlsx)
  • Excel Macro-Enabled Workbook (*.xlsm)
  • Excel Binary Workbook (*.xlsb)

If you are using the older MDB format, you must choose the following file format for your Microsoft Excel Workbook.

  • Excel 97-2003 Workbook (*.xls)

There is no need to panic if you choose the wrong format. You can change it easily. Simply open your Excel Workbook and use File and then Save As and choose Browse. In the next dialogue, change the Save as Type to the required format.

The next step is to download the MYOB or Xero data using OdataLink.

  1. Go to app.odatalink.com OData Feeds page.
  2. Find the OData feed you want to use and click Copy.
  3. Go to Excel.
  4. Using the Data ribbon, click Get Data, then choose From Other Sources and OData Feed
  5. In the next dialogue, paste the URL you copied and click OK.
  6. In the next dialogue, tick the option to select multiple items. Tick the endpoints you want to include and click the arrow next to the Load button and choose Load To.
  7. In the next dialogue, choose Table since you want the data to appear as physically as tables within Excel. You can untick the option to load into the data model as it is not required. Finally, click the Load button.

Once you have done these steps, you will get one sheet for each endpoint you included. We strongly recommend you rename the sheets and choose meaningful names.

Microsoft Excel Worbook with MYOB or Xero Data pulled from OdataLink

Transforming the tables using Power Query

Power Query is native within Microsoft Excel. Not only does it allow you to download the data, but it also provides steps to transform it. With it, you can:

  • Filter your data
  • Sort your data
  • Add new columns
  • Rename columns
  • Remove columns
  • etc.

To edit your tables within Power Query, follow these steps.

  1. Go to the Data Tab in the Microsoft Excel Ribbon and ensure Queries & Connections is selected.
  2. On the Queries & Connections panel on the right, right-click on one of the tables and choose Edit Query.

If you are new to Power Query, we strongly recommend checking out our Introduction Tutorial for Power Query using OdataLink.

Linking your Microsoft Excel Workbook with your Microsoft Access Database

Finally, once you have finished transforming your data and have saved your workbook, you are now ready to link it with your Microsoft Access database.

  1. With your Microsoft Access database open, go to the External Data tab and choose New Data Source \ From File \ Excel.
  2. Click the Browse button and select your Excel Workbook.
  3. Select the option to Link to the data source by creating a linked table and click OK.
  4. Select the sheet to link and click Next
  5. Ensure the option First Row Contains Column Headings is ticked and click Next.
  6. Enter a name for your table and click Finish.

Repeat the steps for each table you need to link. Once done, you should see the tables appears on the Access Object Panel on the left-hand side. You can click on each one to confirm the data appears as intended.

Linked Microsoft Excel Workbook within a Microsoft Access Database

Refreshing your Microsoft Excel Workbook

Refreshing the data is simple.

  1. Open your Microsoft Excel Workbook
  2. Go to the Data tab
  3. Click the Refresh All button
  4. Save the workbook

Even better, these steps can be automated using programming.

Refresh Workbook VBA Macro

The following VBA Macro can be added to your workbook to refresh and save it.

Download VBA Macro

Refresh Workbook VB Script

The following VB Script can be used to refresh and save any workbook.

Download VB Script