Microsoft Access and .Net Web Services

Using VBA in MS Access (or any VBA-enabled tool such as Excel, Word, etc.), we can interact with web services to push/pull data to/from a database such as Microsoft SQL Server. Below we are consuming a .Net Web Method in our Web Service that returns a DataSet, creating an XML Document and looping through it to insert data into our local MS Access table. For this implementation we referenced “Microsoft XML, v6.0” under Tools | References in the Access VBA module.

Dim objXML As Object
Dim xmldoc As MSXML2.DOMDocument60
Dim xmlNode As MSXML2.IXMLDOMNode
Dim getNode As MSXML2.IXMLDOMNode
Dim strURL As String
Dim sSQL As String

Set xmldoc = New DOMDocument60

Set objXML = CreateObject(“MSXML2.XMLHTTP”)
strURL = “” & sCustomerName
objXML.Open “GET”, strURL, False

xmldoc.loadXML (objXML.responseText)
If Len(xmldoc.Text) = 0 Then
MsgBox “No data found.”
Set objXML = Nothing
Set xmldoc = Nothing
Exit Sub
End If

Set xmlNode = xmldoc.selectSingleNode(“//NewDataSet”)

For Each getNode In xmlNode.childNodes
sSQL = “Insert into tblLocalData (CustomerID, ShipToID, CustomerName, ShipToName) ”
sSQL = sSQL & ” VALUES(‘” & getNode.childNodes(0).Text & “‘,'” & getNode.childNodes(1).Text & “‘,'” & getNode.childNodes(2).Text & “‘,'” & getNode.childNodes(3).Text  & “‘)”
CurrentDb.Execute sSQL
Next getNode

Set objXML = Nothing
Set xmldoc = Nothing
Set xmlNode = Nothing
Set getNode = Nothing