Can you consume xml from access?

Question

I'm researching the viability of a planned project that is to consume some data from a web server.

Not being an access developer myself I wanted to know:

1. Is it possible to consume xml from an access database?
2. Can xml be consumed over an authenticated connection?
3. Can xml be consumed over an encrypted connection (https)?
4. What are the 'gotchas' of this process?

Answer 1

Access as of the 2003 version has full support of web services. There is a wizard that you can use to point at a web service definition and generate an access database and forms to access it.

Here is a tutorial for consuming web services

Answer 2

First of all, you can use the web services add in (soap tool kit). Dispite silly comments here, that web add-in for office received a update December 12, 2007

http://support.microsoft.com/kb/937961

However, really, for grabbing XML from a web site you can write just a few lines of code to do this in ms-access if you use the MS-XML library

  Public Sub GetQuote2()

     Dim objXML           As Object
     Dim strSymbol        As String
     Dim strURL           As String
     Dim strWFormat       As String

     Set objXML = CreateObject("MSXML2.XMLHTTP")

     strURL = "http://ca.finance.yahoo.com/d/quotes.csv?s="
     strWFormat = "&f=sl1d1t1c1ohgv&e=.csv"


     strSymbol = "MSFT"

     objXML.Open "GET", strURL & strSymbol & strWFormat, False
     objXML.Send

     Debug.Print "Symbol = " & Split(objXML.ResponseText, ",")(0)
     Debug.Print "Trade  = " & Split(objXML.ResponseText, ",")(1)
     Debug.Print "Date   = " & Split(objXML.ResponseText, ",")(2)

  End Sub

Output when above run:

Symbol = "MSFT"
Trade  = 24.62
Date   = "9/4/2009"

The above code example happens to "GET" a CSV file, but in most cases that web service will give you a xml file or even document. Using the MSXML library also means you have full xml parsing at your fingertips.

You can/could also write out the xml text string to a local file and use the XML import features we have in ms-access.

So access does have an xml import ability. With xml import support + the MSXML library this takes VERY FEW lines of code to grab xml from a web site. Access 2010 will have additional web services support.

For all intensive purposes, I really don’t recommend using the soap web tool kit (the 2003 add in code) for office since the above code is far more simple, and a LOT less hassle and code.