Using a combination of ADO and Microsoft's new XML tools, you can retrieve data from across the Internet. Peter Vogel shows you how with code that will work right now.
I so frequently miss the boat, technologically speaking, that I wonder how I survive at all in this high-tech profession. As I work in the world of XML, I'm doing a great deal of development using Microsoft's XML tools. However, it wasn't until I was reading Ted Pattison's excellent Building Distributed Applications with COM+ and Visual Basic 6.0 that I realized how those XML tools could be applied in my Access environment.
In this article, I'm going to show you how to use a combination of ADO and XML objects to send data back and forth across the Internet. In the first section of the article, I'll show you how to request an ASP page from your Access application, have that page create a Recordset, and then pass that Recordset back to your Access application. In the next section, you'll see how to return that Recordset back to the Web server to update the database that it was drawn from. The techniques that I'll show you work right now with any existing Internet Information Server or Personal Web Server site.
To make this work, you don't need Access Data Pages or Access 2000. All you need is two dozen lines of code, ADO on your client and Web server, and the XML tools that are installed automatically with Internet Explorer. I developed and tested all of this code on my laptop with Personal Web Server before rolling it out to one of my client's applications.
Retrieving from the Net
Retrieving data over the Net takes advantage of the ADO Recordset object's Save method. The Save method can persist a Recordset's data into any object that exposes the IStream interface. What the object with the IStream interface does with the stream of bytes that comes from the Save method depends on the object. A PropertyBag object, for instance, makes the stream available to the program using the PropertyBag as a byte array, through a property called Contents. The ASP Response object, which is what I'm interested in here, returns the string to whatever client requested the ASP page. Effectively this means that a Recordset saved to the ASP Response object is routed back to the program that asked for the page to be run.
As an example, assume that this VBScript code is in a page called Recordset.ASP on a Web server called MyServer:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.LockType = 4
rs.Open "Select * from Customers", _
rs.Save Response, 1
Set rs = Nothing
This code creates an ADO Recordset object, fills it with records from the Customer table in whatever database is pointed to by the NWind.UDL datalink file, and saves those records to the ASP Response object. The default settings for a Recordset object are just fine if all you want to return is a read-only Recordset. In this sample code, I've set the Recordset's LockType property to 4 for Batch Optimistic locking only because in the next section of this article, I'm going to update the Recordset.
Setting the second parameter of the Save method to 1 causes the data to be saved in XML format. Saving the data in XML format converts the contents of the Recordset into a "tag-and-text" format that looks very much like HTML and travels well over the Net. Typical results look like this:
Address='Obere Str. 57' City='Berlin'
CompanyName='Ana Trujillo Emparedados y helados'
ContactName='Jose Trujillo' ContactTitle='Owner'
Address='Avda. de la Constitución 2222'
City='México D.F.' PostalCode='05021'
Country='Mexico' Phone='(5) 555-4729'
That's all the code that's required on the server side to retrieve data. On the client side, your Access program requires some VBA code to request the ASP page and retrieve into a Recordset that the page produces. The following VBA code takes advantage of the Recordset's Open method's ability to accept the name of a file created by the Save method and recreate the original Recordset. To request the ASP page, receive the data, and recreate the Recordset, all you need is this:
Dim rs As Recordset
Set rs = New Recordset
rs.Open "http://MyServer/Recordset.ASP", , , , _
Passing an HTTP address to the Open method causes Windows to go out on the Internet to request the file, just as passing a filename would cause Windows to request a file from its own file system. The ASP page code then routes the XML back to the client that requested the page, and the Recordset's Open method recreates the Recordset. The resulting Recordset on the client will be a client-side, static Recordset.
With the Recordset retrieved, you can do almost anything that you want in your Access code: Sort, filter, or move the data to a form to display it. There's one limitation: Since this is a disconnected Recordset and it doesn't have a valid connection to a database, it can't be bound to an Access 2000 Form's Recordset property.
Moving outside of Access, since what was generated by the ADO Open method is an ordinary request for a Web page, no special processing, security settings, or open ports are required on either the server or the client. Since the output that comes back is pure text, no special settings are required to have the data routed back to the client either. It just works.
The Recordset in my sample code is generated straight from a database, which could be considered to violate the normal boundaries between the presentation and data tiers. However, you can generate a Recordset "on the fly" without pulling it out of a database. This lets you use Recordsets as a handy mechanism for moving data around that isn't dependent on any particular table layout. The preceding code would still work.
Finally, you actually don't have to use ADO on either the client or server. On the server side, the ASP page could generate the XML document without using ADO and save it to the Response object; on the client side, the resulting XML document could be processed using XML rather than ADO. It's just that ADO makes it all so easy.
Updating on the Net
Getting the data back to the server for updating requires a little more work than retrieving data because ADO doesn't provide as simple a way to route the Recordset back to the server that you retrieved it from. To get your data back to the client, you'll need to issue a new request to the server and pass your Recordset as part of that request.
Fortunately, Microsoft's XML tools include an object that will do just that for you. The XMLHTTP object (called XMLHTTPRequest in earlier versions) is designed to pass XML documents to programs over the Internet using the standard HTTP protocol. You have the XMLHTTP or XMLHTTPRequest object installed on your computer if you've installed any version of Internet Explorer 5.0 or better. You can also download the current version from http://msdn.microsoft.com/xml.
Before creating the XMLHTTP object, you must first go to your Tools | References dialog box and add a reference to Microsoft XML. After creating the object, all you have to do is specify the program to accept your data on the server, the method of transmission, and the XML data to be sent.
In this case, I'm going to send my data back to an ASP page called ProcessData.ASP. I'm going to use the Post method to send the data, as that allows far more data to be sent than the alternative (Get). The Open method of the XMLHTTP object lets me set all of that up. The third parameter to the Open method allows you to specify whether you want to use synchronous or asynchronous processing (I've chosen synchronous in this example):
Dim xmlHTTP As xmlHTTP
Set xmlHTTP = New XMLHTTP
xmlHTTP.open "POST", _
Since the XMLHTTP object requires that I send an XML document, I have to convert my Recordset back into an XML document. Fortunately, the Microsoft XML document object also supports the IStream interface so I can create an XML document just by saving my Recordset to a DOMDocument object. The one catch here is that you must have ADO 2.5 or better installed on your client. On Windows 2000, ADO 2.5 will be installed, but you might have to change your reference in Access from ADO 2.1 to ADO 2.5. On other versions of Windows, you'll need to download the latest version of the Microsoft Data Access Components from www.microsoft.com/data.
Here are the three lines of code that convert an ADO 2.5 Recordset into an XML document:
Dim xmlDoc As DOMDocument
Set xmlDoc = New DOMDocument
rs.Save xmlDoc, adPersistXML
With those preliminaries taken care of, I can use the XMLHTTP object's Send method to request my ASP page. The Send method requires a single parameter, the XML document:
Since I'm sending XML data using standard HTTP protocol, I can send this data to any program on any operating system on any computer anywhere on the Net. When the program on the other end is run as a result of my request, it can retrieve and process the XML data that I've sent. Once again, however, ADO and ASP make the process simple.
In ASP, all data that comes to the server as part of the request for the page can be retrieved from the ASP request object. To recreate my Recordset, I only have to pass ASP Request object to the Recordset's Open method. In the following ASP code, the 256 in the last parameter in the Open method is equivalent to using the adCmdFile constant in VBA:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open Request, , , , 256
With the Recordset recreated on the server, I can reconnect it to the database by setting the Recordset's ActiveConnection property. To process all of the updates made on the client, I just call the Recordset's UpdateBatch method:
rs.ActiveConnection = "File Name=c:\NWind.UDL"
It's only good manners to respond to any request, so the ProcessData.ASP should send some sort of acknowledgement back to the client that sent it the data. I'll do that by using the Response object's Write method to send a message back to the client:
Response.Write "Data Updated"
Back in my Access program, I can retrieve the response to my request by looking in the XMLHTTP object's ResponseText property:
If anything goes wrong with your ASP processing, you can find the error message that ASP generated in this property also. If I wanted to send more complicated information back, it would probably be a good idea to create an XML document. XML documents are more structured than simple text and, thanks to the existence of standard XML parsers, easier to read.
I've included the two ASP pages and a very simple Access 97 program that demonstrates this technique
Find the download in theon this page (called 011VOGEL.zip)