In the clouds¯ ~ Using Microsoft Access to query and update Web sites

Figure 1: Sample task list

In this article I am going to describe how you can use an Access Database and VBA to manipulate a Web 2 application called HighRise, a customer relationship management (CRM) tool from the highly successful Web2 company, 37Signals.  Why pick this online product, well the main reason is that I love using it, it has a well-written Application Programming Interface (API) and it is free for your first two hundred contacts. Why might this be relevant and interesting to you, the reader? Because you will be performing these tasks on a database that is hidden behind the security of a website, something that has always been beyond the abilities of Access. The reason I love Highrise, is that it orders my people specific tasks (e-mailing/calling) into a wonderful list of fuzzy dates comprising today, tomorrow, this week, next week, and later and fuses that with specific dates and times like 12-june-08 4pm (see the sample task list in figure 1).  Highrise also keeps track of names, addresses, correspondence relating to people and companies in quite a versatile environment. Anyway the functionality of the website is not important, what is important is that you can interact with the data in the Highrise website through the API by posting and retrieving XML.  This allows you to extend the feature list of the online application and it allows you to merge information on a website with other applications on your desktop.  More than likely this is going to be a Microsoft Access database if you are reading this article.   Note: Other prominent websites that have online API"™s include Amazon, FaceBook, SalesForce and  Microsoft has a beta release of SQL Server aimed fair and square at this market. Part 2 of this article: Using Microsoft Access to post data into Web sites

Highrise Website And Download Samples
 

The sample that I have set up (shown in figure 2) will get a list of people, will add a person and delete a person.  You will find VBA code under the buttons in the download Access database. 
 
To make the sample work, you need to set up your own Highrise website. Do this by heading to http://highrisehq.com/ and clicking on the signup for free link.  Work through the signup process until you have a Highrise sub-domain URL of your own plus a username and password.  Once you're up and running, add a few people into Highrise as you will need this for your sample. 
 
To use the technology in this sample, you need a copy of a Microsoft library called MSXML.  To  download MSXML, head to http://www.microsoft.com/downloads/ and search for MSXML6.  Now you should be ready to run the samples.

 

Figure 2: Access Sample Database in action

Get a List of People from the Highrise Website into Your Access Application
 

Let's look at the code that you need to retrieve a list of people.  I will do this without including any of the necessary error checking because I want you to see that the code is not all that complex.

Listing 1: Code to retrieve a list of people (without error checking)

Private Sub cmdGetPeople_Click()

  ' Get a list of people from your Highrise account
  'Head to (List All) at the following address for full XML
  'http://developer.37signals.com/highrise/people.shtml
  
  Dim objSvrHTTP  As ServerXMLHTTP
  Dim objXML As DOMDocument
  
  
  Set objSvrHTTP = New ServerXMLHTTP
  objSvrHTTP.Open "GET", txtURL & "/people.xml", False, _
   CStr(txtUserName), CStr(txtPassword)

  objSvrHTTP.setRequestHeader "Accept", "application/xml"
  objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  objSvrHTTP.send
  
 
  'Load Highrise response into an XML document object and
  'save it to the harddrive
  Set objXML = New DOMDocument
  objXML.LoadXML objSvrHTTP.responseText
  objXML.Save "c:\highrisePeople.xml"
 
  'If you want better structured XML, use this code
  txtXML = objXML.xml
  'even though this code is quicker
  'txtXML = objSvrHTTP.responseText  
 
Exit_Procedure:
  On Error Resume Next
  Set objSvrHTTP = Nothing
  Set objXML = Nothing
  Set objSvrHTTP = Nothing  
  Exit Sub
  
End Sub
 

Let's look at the code in some detail. To start the routine of, you need to declare
 

Dim objSvrHTTP  As ServerXMLHTTP
  Dim objXML As DOMDocument
 

ServerXMLHTTP allows you to communicate with the API and DOMDocument gives you a number of code tools to process the XML that is transmitted from the website.  Both of these objects come from a well supported library from Microsoft called MSXML (see notes on installing MSXML).
 
First you need to set up the request that you send to the website to retrieve a list of people.  This is managed through the GET command and is wrapped with the website address, user name and password.  To make it it more obvious, here is what the GET command might look like for your Highrise subdomain.
 

objSvrHTTP.Open "GET", "http://joessample.highrisehq.com/people.xml ", False, _
   'JoeBloogs'¯, 'joesmum'
 

Send Command and Response text
 

You retrieve data from a website using the "GET"¯ which returns an XML string in the response text of the ServerXMLHTTP object.  Now have a look at the following code snippet to see how we populate a text control with the API's XML data.
 

  objSvrHTTP.send
  txtXML.value = objSvrHTTP.responseText  
 
 

Working with the good XML

If all is working well, you will now be in a position where you need to do something with the XML that has been returned using the ServerXMLHTTP Send method. Have a look at the XML displayed in the text box on the right hand side of Figure 2.  As you can see it is orderly but unlike a table or a query.  To do something with the XML (which is text with tags), you can
 
1)      use traditional text manipulation functions such as InStr, Left, Mid and Right
2)      save the XML to a text file and then (try) load that into an Access database using XML import commands that can be found in the Access menus, macros or VBA.
3)      You can manipulate the XML using the DOMDocument object, a tool that will allow you to do almost anything with an XML file if you can work out how to use it.
 
In this code snippet, I show you how you can save the XML to a text file and display the XML in a text box.
 

  Set objXML = New DOMDocument
  objXML.LoadXML objSvrHTTP.responseText
  objXML.Save "c:\highrisePeople.xml"
 
  'If you want better structured XML, use this code
  txtXML = objXML.xml
 

Tip: if you want to have a easy to read XML, use the load XML method of the DOMDocument.
 

Error handling

There are two types of errors that you can expect when dealing with a Web2 API, status codes from the API and VBA errors.  In the download sample you will find the code to handle the website status codes.  Whilst the status codes and what they mean will vary from site to site, generally they will work something like this. 
 

  objSvrHTTP.send
  
  If objSvrHTTP.status = 200 Then
    
    MsgBox "List of first 50 people has been retrieved "
  ElseIf objSvrHTTP.status = 401 Then
    
    MsgBox "Listing failed with error# " & objSvrHTTP.status & _
     "  Check your username and password"
    
  Else
    MsgBox "Listing failed with error# " & objSvrHTTP.status & _
     "  " & objSvrHTTP.statusText
  End If
 

The second type of error that you need to handle is the VBA errors that are generated in this environment.  In the following code listing that you will find at the bottom of the download sample, you'll see how a trap a Internet connect error.

Error_Handler:
  Const NOINTERNETAVAILABLE = -2147012889
  
  Select Case Err.Number
      
    Case NOINTERNETAVAILABLE
      
      MsgBox "Connection to the internet cannot be made or " & _
       "highrise website address is wrong", vbCritical, _
       "Listing of People cancelled"
      
    Case Else
      MsgBox "Error: " & Err.Number & "; Description: " _
      & Err.Description, _
      vbCritical, "Problem in subroutine called cmdGetPeople"
      Resume Exit_Procedure
      Resume
  End Select
 

Tip: if you look carefully at the code you will see that there are two Resume commands together.  If you are in Debug mode, you can drag the yellow arrow to the line with only Resume on it.  Now press the F8 key and Debug will return you to the line where the error occurred.

Summary ~ it's time to get your head in The Clouds

A lot of the websites are calling this Web2 technology "In the Cloud"¯ software; whilst the name is not important, getting data from "in the cloud"¯ and into your local Access application database is. At the moment, the main stumbling block for Access programmers is that most of the samples online are written in .NET, Ruby, PHP and other Web languages. But rest assured that this is likely to change pretty fast over the next couple of years as more writers start posting samples for VBA on the web.

  Read part 2 of this article: Using Microsoft Access to post data into Web sites

  Your Sample Database Is Called   "dbj_Highrise.zip"

Sample database is suited to all versions of Access 2002 +

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Other Related Articles That You Might Want to Read at Our Site

Remote Updates of a Back-End Database

Samples of Good Access Programming Practises

Automation Of Word

External Links

Microsoft XML Core Services (MSXML) allows customers to build high-performance XML-based applications read more