Clever Geek Handbook

Can you use xml to access?

I am investigating the viability of a planned project that needs to consume some data from a web server.

Not being an access developer, I would like to know:

  • Can xml be used from access database?
  • Can xml be used over an authenticated connection?
  • Can xml be used over an encrypted connection (https)?
  • What are the "gotchas" of this process?
+2


source to share


2 answers


First of all, you can use Add Web Services (Soap Toolkit). Silly comments notwithstanding, this web-based office add-in got an update on December 12, 2007.

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

However, indeed, to grab XML from a website, you can write just a few lines of code to do it in ms-access if you are using 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 is "GET" the CSV file, but in most cases the web service will provide you with an xml file or even a document. Using the MSXML library also means you have complete xml parsing at your fingertips.

You can also write the xml text string to a local file and use the XML import functions we have in ms access.

Thus, access has the ability to import xml. With the xml + import support in the MSXML library, it takes VERY FEW lines of code to grab the xml from the website. Access 2010 will have additional support for web services.

For all intense purposes, I really don't recommend using the soap toolbox (2003 add code) for the office as the above code is much simpler and the LOT is less hassle and code.

+3


source


Access 2003 version has full web services support. There is a wizard that you can use to define a web service definition and create an access database and forms to access it.



Here is a tutorial for consuming web services

0


source






More articles:


All Articles