Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > API Authentication >

Has anyone connected an Access 2010 database to Xero?

Started by Stephen Bradley -   in API Authentication

Looking to build our own inventory management system. Unleashed has had a volatile pricing model recently and simPRO isn't quite the right fit.

Access 2010 allows you to connect to web services, so I'm wondering if anyone has done this.
Sorry, I don't know the answer, but I'm interested to know how to do this if you find out.

Are you wanting to use the Xero API using VBA in Access 2010, or are you hoping that the Xero data (invoices, contacts etc) would appear as linked tables in Access?

Ian Viney  

The API uses a method called OAuth to connect. It would be quite difficult (almost impossible) to connect to the API directly from something like an office application.

I know of a developer who was starting to look at building an ODBC driver to connect to the API which could then be used to connect via Excel or Access. If you want to vote and subscribe to updates on a related request in our uservoice forum, I will update that: http://xero.uservoice.com/forums/28866-integration-with-other-products/suggestions/2143205-develop-module-to-import-from-excel

Ronan Quirke (Community Manager)  

Yes for sure. For now we're going to try Unleashed to see how it works out for us, but an ODBC driver to connect via Access would be perfect.

Stephen Bradley  

Hi Stephen,

Not sure if you're still monitoring this post - it has been a while.

Are you still interested in a solution to extract data from Xero into a VBA based system like Access or Excel.

I'm working on something at the moment and have a proof of concept. Let me know if you're still looking at this/interested in it.


Scott Barber  

Hi Scott, I'm academically interested. We ended up having to cancel our migration to Xero due to significant problems with Unleashed, the inventory add-on. I'd definitely be interested to see what you've put together but wouldn't be able to make any kind of immediate use of it, sadly.

Stephen Bradley  

Hi Stephen, that's a shame.

It's current form is just a module allowing you to public AUTH and then GET and POST via the usual API endpoints. I'm currently using it within an Accounting Practice to streamline processes with the data contained within Xero. I.e. pull custom reports, transaction details for reviewing, post regular journals directly based on this information.

Excel isn't the correct tool for everything, but accountants sure do love it.

Scott Barber  

Hi Scott, wow and of course if you have built a module in Excel for this, it would be easy enough to adapt that to the access object model for even more advanced stuff. Very, very cool. If this had existed when I first put in this inquiry, I could have kept Xero, dumped Unleashed, and just whipped together a custom Inventory application with Access and SQL Azure. Facepalm of regret.

If you are willing to share your code, or allow me to have it for a reasonable price, I'd still be highly interested as I am probably looking at a few projects down the road that will potentially involve Xero. Feel free to contact me privately if so, sbradley@vitaltheory.com.


Stephen Bradley  

Hi Scott,
I would be interested too!

Ian Viney  

HI Scott

I am trying to create a Management Accounts Pack from VBA to pull in from Xero - if this something you are able to assist with I would be very interested in speaking with you.

I can be contacted on poonam.mawani@hendersonscott.com


Poonam Mawani  

I just want to post invoices and payments from an excel spreadsheet so if your module could give me a head start I would really appreciate it.

I find it strange that I can import invoices from CSV files but not payments

John jda@arundell.net

John Arundell  

Hi Scott,

I would be interested in your API/module. I would like to interact (Post and retrieve) with Xero from Access. If you could let me know of your progress - that would be great. I can be contacted at cnoel@austcollege.com.au

Chris Noel  

Hi Everyone,
(I posted this on the business forum earlier, thought you all might be interested as well)
I develop the Office Integration Tools for Xero. My next release is for MS Excel (see an early screenshot here http://www.quickwindevelopment.com/?p=3311).
I plan to give access to the entire API through a simple menu system that will dump all of the data into a table on your Excel Worksheet. From there you can do whatever reports you want. I'm also open to suggestions and feature requests.

I don't charge subscription fees for my software, it's a one off payment and all future updates are free. So if you're keen to help shape the development of this, email me at ruairi@quickwindevelopment.com and request to be a beta tester. All beta testers get a free copy of the final product!

Also, check out the Outlook Integration Tools


Ruairi M  

Ruairi, presumably one can use VBA as well as the menu system to access the API?

Ian Viney  

Let me clarify, by access to the API I mean access to the data provided by the API, not some sort of wrapper for VBA. I'm looking to allow you to select and filter data available via the API and drop it anywhere you want in Excel. You can then write your own custom reports with this data.
I'll then extend functionality based on feedback from the beta testers and community.

Ruairi M  

Yes I also am very interested in an ODBC driver for Xero. In the past I have done quite a lot of work with Sage in house accounting software pulling account balances and transaction information into Access applications and customised Outlook forms. It works well and ODBC is a plus factor when clients select an accounting system and wish for this sort of customisation. With the move towards Cloud software the need for a good Cloud software application which provides ODBC seems to me to be of increasing importance.

I'm currently experimenting with using REST in VBA. I've used this before with urls that require no authorisation credentials. I've yet to work out how you use OAUTH within VBA but it should be possible.

As a simple illustration, here is an example of a VBA query directed to the US Post Code service which returns information to an Access Application.

Private Sub Command0_Click()

Dim zip As String
Dim query As String
Dim zipResult As New MSXML2.DOMDocument
Dim zipService As New MSXML2.XMLHTTP
Dim Display1 As String

zip = Me.Text1

query = "http://webservicex.net/uszip.asmx/GetInfoByZIP?USZip=" & zip
zipService.Open "GET", query, False

zipResult.loadXML (zipService.responseText)

Display1 = "City = " & zipResult.selectSingleNode("//CITY").Text & vbCrLf
Display1 = Display1 & "State = " & zipResult.selectSingleNode("//STATE").Text & vbCrLf
Display1 = Display1 & "Area Code = " & zipResult.selectSingleNode("//AREA_CODE").Text & vbCrLf
Display1 = Display1 & "Time Zone = " & zipResult.selectSingleNode("//TIME_ZONE").Text & vbCrLf

MsgBox Display1

End Sub

As earlier posters have commented, an ODBC driver for Xero would be a big plus - many of the SAS products like Sales Force and Dynamics CRM already have this facility.

But I've no idea how complex writing an ActiveX odbc object might be. Ideally it should allow table joins - which I understand adds complexity.

Pending further ODBC development progress, I'm currently looking at pursuing the REST approach. Has anyone any experience of OAUTH in a language in VB ?


Peter Kaye  

Hi Scott,

We are trying to see if we can import some of the payroll data into an Access database to conduct reporting. I am really interested in understanding how you connect to Xero using VBA. I can be reached at golam.bhuiyan@callida.com.au. I would really appreciate any help you can provide.


Golam Bhuiyan  

Hi Golam,
If you want to report on Payroll data, there are much easier ways to do so. I wouldn't advise trying to connect to Xero using VBA unless you wanted to do it as a fun development project.

My Excel addon lets you download all data from the API so you can make your own custom reports. You can easily sync this data to MS Access so you can use its reporting tools. I also support MS Report Builder natively, so you can use that instead of Access.


Ruairi M  

Hi Golam,

I agree with Ruairi - VBA isn't the right tool if you're just trying to extract data for reporting. I can recommend the tool fro Quick Win development for this purpose - as it is very well priced when compared with the development cost of doing anything with VBA.

However - if you're looking for help with a broader project (like integrating Xero data for a number of clients into your current systems and processes) - feel free to send me an email, I would be happy to help. Note this is both reading and writing, I.e. I've created projects for clients that extract data into standard reports into an excel worksheet, and then add the ability to write standard journals back into Xero.


Scott Barber  

Not sure if this is still alive...but I might have your solution here

Tapaha Robati