In part one of this article, I demonstrated how you could retrieve data and save it into XML using Microsoft Access and MSXML. In this article I am going to show you how you can add new data to a Web2 website (“in the clouds”).

To achieve this, I show you how to use an Access Database and VBA to manipulate a Web 2 website called HighRise, a customer relationship management (CRM) tool from 37Signals.com.

Adding a record

To get data into a Web2 website requires you to create an XML string with your new information then Post that information. This is subtly different to retrieving data as shown in part one of this article. The trickiest thing about programming in this environment is just finding a correctly structured (VBA code) sample to borrow from. Generally the samples online are written in trendy languages such as .Net or Ruby. Once you have that sample, you then fill the XML by plugging your data between the XML tags. Let's see how we do that. Adding a new person to the Highrise Website from your Access application

Let's look at the full code that you need to add a person. This is analogous to an append query in Access and as you will see, it takes a lot more code to achieve the same result. I illustrate this with error checking because error testing is important to verify the data has been added. I will break down the code with descriptions in subsequent sections of the article.

Listing 1: Code to add a person

Private Sub cmdAddPerson_Click()
  
  
  On Error GoTo Error_Handler
  
  Const NOINTERNETAVAILABLE = -2147012889
  
  Dim objSvrHTTP As ServerXMLHTTP
  Dim varProjectID, varCatID, strT As String
  Set objSvrHTTP = New ServerXMLHTTP
  
objSvrHTTP.Open "POST", txtURL & "/people.xml", False, CStr(txtUserName), _
   CStr(txtPassword)
  
  objSvrHTTP.setRequestHeader "Accept", "application/xml"
  objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  strT = ""
  strT = strT & ""
  strT = strT & "  " & txtFirst_name & ""
  strT = strT & "  " & txtLast_Name & ""
  strT = strT & "  "
  strT = strT & "  Doe Inc."
  strT = strT & "  A popular guy "
  strT = strT & "  "
  strT = strT & "    "
  strT = strT & "      "
  strT = strT & "        
" & txtemail_address & "
"
  strT = strT & "        Work"
  strT = strT & "      "
  strT = strT & "    "
  strT = strT & "    "
  strT = strT & "      "
  strT = strT & "        555-555-5555"
  strT = strT & "        Work"
  strT = strT & "      "
  strT = strT & "      "
  strT = strT & "        555-666-6666"
  strT = strT & "        Home"
  strT = strT & "      "
  strT = strT & "    "
  strT = strT & "  "
  strT = strT & ""
  
  objSvrHTTP.send strT
  
  If objSvrHTTP.status = 201 Then
    
    txtXML = objSvrHTTP.responseText
    MsgBox "Person Added"
    txtPersonId = TagDetails_FX(txtXML, _
                   "", "")
    
  ElseIf objSvrHTTP.status = 400 Then
    
    MsgBox "Adding Person failed with error# " & _
     objSvrHTTP.status & _
     "  " & objSvrHTTP.statusText & vbCrLf & vbCrLf & _
     "Check email address or ???"
    
  ElseIf objSvrHTTP.status = 401 Then
 MsgBox "Adding Person failed with error# " & objSvrHTTP.status & _
    "  " & objSvrHTTP.statusText & vbCrLf & vbCrLf & _
    "Check Username, password"
    
  Else
MsgBox "Adding Person failed with error# " & objSvrHTTP.status & _
     "  " & objSvrHTTP.statusText
  End If
  
  
Exit_Procedure:
  On Error Resume Next
  Set objSvrHTTP = Nothing
  
  Exit Sub
  
Error_Handler:
  Select Case Err.Number
      
    Case NOINTERNETAVAILABLE
      
MsgBox "Connection to the internet cannot be made or " & _
       "HighRise website address is wrong", vbCritical, _
       "Adding of People cancelled"
      
    Case Else
      MsgBox "Error: " & Err.Number & "; Description: " _
      & Err.Description, _
vbCritical, "Problem in subroutine called cmdAddPerson"
      Resume Exit_Procedure
      Resume
  End Select
  
End Sub

Plugging your data into the correct XML structure

Even though it is slightly out of order lets first look at how you build your data into an XML string. In the sample code in listing 1, you will find quite a long section where I build a string called strT. This is the XML that holds your "new person". In the code scrap below, I show you how you add the first and last names of a person between the appropriate XML tags. You will do this sort of thing to all the XML that you post online.

strT = strT & ""
strT = strT & " " & txtFirst_name & ""
strT = strT & " " & txtLast_Name & ""
strT = strT & ""

How or where you get that data from is going to vary greatly between applications. You might use a recordset code or might take user input from the screen as I've done in my sample. The final XML will always end up looking very similar, basically XML tags with your data in the middle of the tags.

Setting up the post command

To start the routine off, you need to declare the MSXML object objSvrHTTP that moves and retrieve the XML data from the Web site. Then you need to set up the request that you send to the website to add a person. This is managed through the PUT command and is wrapped with the website address, user name and password and the XML that wraps up your data.

Dim objSvrHTTP As ServerXMLHTTP
Dim varProjectID, varCatID, strT As String
Set objSvrHTTP = New ServerXMLHTTP

objSvrHTTP.Open "POST", txtURL & "/people.xml", False, CStr(txtUserName), CStr(txtPassword)


objSvrHTTP.setRequestHeader "Accept", "application/xml"
objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
objSvrHTTP.send strT


In the final line you will see the strT variable which holds your new person XML that I described before.

Checking the post worked using Status Codes

There are two types of errors that you can expect when dealing with a Web2 API, status codes from the API and VBA errors. When adding data, the website status codes that are returned are very important. Whilst the status codes and what they mean will vary from site to site, generally they will work something like this.

If objSvrHTTP.status = 201 Then
Posting was successful
Else
Posting failed, inform user
End if

After adding information to the website, you may also be interested in the returned XML because it may hold things like the unique identifier of the record that you just posted. This can be found in the ResponseText property.

If objSvrHTTP.status = 201 Then

txtXML = objSvrHTTP.responseText
MsgBox "Person Added"
txtPersonId = TagDetails_FX(txtXML, _
"", "")


Note: The sample code includes a function called TagDetails_FX which allows you to retrieve the value from a specific XML tag in an XML string. I will let you explore this function yourself. In my sample code (shown above), I extract the unique record identifier that Highrise returned in the ResponseText property by filtering the XML with the TagDetails_FX function.

Highrise Website and Download Samples

Once again I have used the highrisehq.com and MSXML object from Microsoft for my demonstration. The sample code that I have set up will get a list of people, will add a person and delete a person (not shown in article). You will find VBA code under the buttons in the download Access database. Access 2007 and 2003/2002 format databases are supplied.

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. Read more on adding data at this HTML location

http://developer.37signals.com/highrise/people.shtml

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.

Summary

In this two-part article on the manipulation of Web2 data through a REST API, I focused on what you needed to get started rather than posting a full and complex solution. I hope that I have inspired you to develop VBA solutions in what I think is an important new type of technology for VBA developers, manipulation of “in the cloud” websites. So the next time you use a Web2 Internet application, look around the help files to see if you can find details on programming with an API and you too could be programming “Into the Clouds”

Full article...