Beginning Access 2002 VBA (Programmer to Programmer) - page 147

The above XML file is simply a copy of the Customers table. When viewed within Internet Explorer it takes on a different appearance.

As you can see this is similar to HTML and tags are used to layout the page. Note the minus signs to the left of the page. Clicking the symbol will contract the tree, changing the symbol to a plus sign. Clicking the plus sign has the opposite effect and expands the data tree.

Properly formatted XML is said to be well- formed and meets the following requirements:

• An XML declaration must be the first line in the file.

• All XML tags must have an opening and a closing tag.

• Tags cannot contain comments.

• Tags are case-sensitive.

• All elements must be properly nested.

• All documents must have a root tag. This is the first tag in the document.

Advantages to XML

So why bother - what are the advantages to using XML in the first place? Before looking at some XML examples, let's have a quick overview of the advantages of XML.

• You can define your own XML tags to suit your purpose. For example, the following is a valid XML fragment :

  <Person>
    <Type>Male</Type>
    <height>Big</height>
    <Hair>Black</Hair>
    <eyes>blue</eyes>
  </Person>
  

• Many databases can actually store XML documents (Oracle, DB2, SQL Server).

• XML is an Open Standard - http://www.w3c.org/XML/.

• It is widely used in business to transfer data. For example, a form of XML has been used by the press industry to exchange news information for many years . Have a look at this page which is used by the British Broadcasting Corporation to deliver news content www.bbc.co.uk/syndication/feeds/news/ukfs_news/technology/rss091.xml .

• Permits different systems to interoperate .

• Designed for the Internet.

• Has a very small footprint in the browser.

• Separates content and appearance.

How to Export a Table as XML

Exporting a table to XML is a simple process, and at this level very little about XML needs to be known. Let's see how to export the Customer table as XML. Firstly, in the Database window select tblCompany . With the table highlighted:

1. Click on File on the main Access menu

2. Select File Export

   In the Export Table dialog use the Save As Type drop-down list and select XML Documents

   Important

   Note that Access will add the table name to the dialog as the default file name . You may change this if required.

3. Click the Export button

Now this is where things get interesting. We are given a few different ways that we can export the data as XML:

• Data (XML)

• Schema of the data

Presentation of your data (XSL)

Data

This option will create a simple XML file containing only the table data, as shown in the screenshot below:

This form of XML export uses the default presentation template in Internet Explorer to display the data in a tree structure. If you also select the Presentation of your data checkbox:

Then you also generate an XSL and HTML file. This option is available only if the Data checkbox is selected. The XSL file is much like a HTML template and controls the presentation of your XML data. Rather than use the default template in IE the browser will use the defined XSL file to present your data.

Schema of the Data

This option permits you to export the structure or Schema of the table instead of the data. You may export both the schema and the data in a single operation by checking the Data and Schema of the data boxes in the Export XML dialog. If you want to select Schema of data only, you can then import the resulting file back to Access or SQL Server 2000 Desktop (which is provided free of charge on the Office XP CD-ROM) and have an unpopulated copy of the company table. The XML schema file contains the structure of your Access table including data types, indexes, field names , and any constraints you may have placed on a field. For example, limiting the number of characters allowed in a text field. We will look at this shortly as it provides you with a very easy technique to move schema between different and at times diverse databases.

Clicking on the Advanced button will close the initial XML dialog and open a new dialog. This gives you several additional options when exporting. For example, when exporting a schema you can choose to Include primary key and index information from the file. Note that once you open the advanced dialog you will be unable to return to the initial export screen. The advanced dialog also permits you to include the schema information within the XML file rather than as a distinct document.

It is also possible to use VBA to import and export data as XML. There are two new methods available, ExportXML and ImportXML . For these examples we have set a reference to Microsoft ADO 2.7.

Try It Out-Exporting the Company Table to XML

1. Open a new module and enter the following code into the module window:

   
   
   Sub ExportcustXML()
   
   
   
   
   Application.ExportXML ObjectType:=acExportTable, _
   
   
   
   
   DataSource:="tblCompany", _
   
   
   
   
   DataTarget:="c:\Companyxml.xml", _
   
   
   
   
   SchemaTarget:="CompanySchema.xml", _
   
   
   
   
   OtherFlags:=1
   
   
   
   
   End Sub
   
   
   

This procedure will export the Customers table as XML. The resulting file CompanyXML.xml will be saved to C:\ or any folder specified within the argument. In addition, we are also creating a schema file, CompanySchema.xsl . The OtherFlags option, currently set to one offers you additional control over the way the file is exported.

Other Flags

1. Creating a generic procedure:

   We can also create a nice simple generic procedure for exporting objects as follows . In this case, all that is required is that you pass the table name and the save path to the procedure, and off you go. Enter the following procedure into the module window:

   
   
   Sub ExportXML (tabl As String, path As String)
   
   
   
   
   Application.ExportXML ObjectType:=acExportTable, _
   
   
   
   
   DataSource:=tabl, DataTarget:=path
   
   
   
   
   End Sub
   
   
   

This procedure automates the manual example just seen, and provides you with more flexibility when it is called. The resulting file will look no different to that produced manually. Importing an XML file is just as easy.

Try It Out-Importing an XML File

Before we import an XML file using ADO, we need to export a new copy. We will discuss the reasons for this shortly.

1. In the Database Window, select tblCompany

2. From the main menu select File Export

3. Select XML Documents from the Save As Type dialog, accepting the default name for the file

4. Save the file to C:\

5. In the dialog, accept the default option of Data and Schema of the data , and click OK to finish the export

6. Open a new module and enter the procedure shown below:

   
   
   Sub ImportXML()
   
   
   
   
   Application.ImportXML "C:\tblCompany.xml"
   
   
   
   
   End Sub
   
   
   

This procedure will create a perfect copy of the customer table which already exists within the database. In this case, the table name will be suffixed with the number 1. Each time you run the procedure the table will be recreated as tblcustomer2 , tblcustomers3 , and so on. In addition, there are three arguments available when using ImportXML :

• acStructureAndData , which creates the table and populates it with data

• acStructureOnly , creates the table structure only

• acAppendData , will simply append the XML data to an existing table

OK, so why did we not use the XML file we created earlier via ADO? Well try it and see what happens, you can't import it. ADO creates a type of XML file that is not recognized by Microsoft Access. Access 2002 uses a specific type of XML called element-centric XML, as opposed to the attribute-centric XML used by ADO. Therefore it is not possible to import ADO produced XML without first transforming it using XSLT. XSLT is discussed in Beginning XSLT by Wrox Press (ISBN: 1-861005-94-6).

Creating a Generic Import XML Routine

As with ExportXML we can also create a simple generic procedure which can be used to import XML files.

Sub ImportXML(path As String)




Application.ImportXML DataSource:=path, _




ImportOptions:=acStructureAndData




End Sub

In this case all that is required is the full path to the XML file being imported.

In order to execute the above procedure, open the Immediate window while in the VBE. Press Ctrl + G or select View Intermediate Window using the main menu option. We will import the XML file created in the example above. Simply enter the following into the Immediate window:

ImportXML("C:\ tblCompany.xml")

Return to the database window and notice that a new tblCompany has been created based on the XML file. If you have been testing, you should notice that the table name is followed by a number.

As you can see Access 2002 offers several ways to use XML within a database. In these brief examples we have exported data to XML and then imported an XML file back into Microsoft Access. But what's the point in it all? Well as we seen above, when we looked at the advantages of XML, you are not restricted to exporting and importing to simply Microsoft Access. Suppose a client is using IBM DB2, Oracle, or even SQL Server. XML provides you with a way to export data and table structures in a way that is now universally understood by most major database systems in use with business. You can be almost certain that your client can then read into their system your Access data, or even recreate the table structures in their systems. The use of XML files both to view and share data via the browser is expected to increase, and we can expect to see the XML features of Access improve with the next version.

Reports and XML

When you need to have reports available via the Internet, XML offers you another tool in the arsenal. Similar to how we can save reports as DAPs, it is also possible to save reports as XML files via the Access interface. In this way we can, again, make data available via the Internet. For static data, we provide a link to the XML file as opposed to a connection to the database. However, remember that the data is static and the user will be viewing information at a specific point in time, but then again, that's generally the purpose of a report anyway.

The process used to export a report to XML is identical to that used with a table: select the report, right-click, and choose Export . However, in this case, you will return a variety of documents including a HTML file, the XML File and an XSL stylesheet. Generally to view the data you select the HTML file which acts as the template for the other file groups.

Try It Out-Exporting a Report as XML

1. Select the Ingredients report from the Database window

2. Select File Export from the main menu

3. Select XML Documents from the Save as type list

4. Click the Export button

5. Accept the default Data in the Export XML dialog

6. Click OK

7. Navigate to the folder you saved the files to, and open Ingredients.html using a web browser

   

Notice that we are opening a HTML file, not the XML directly. The HTML file contains a script which calls the required XML and XSL files for viewing. The relevant lines are show as bold in the script below.

SCRIPT event=onload for=window>
  objData = new ActiveXObject("MSXML.DOMDocument");
  objData.async = false;

objData.load("Ingredients.xml");

if (objData.parseError.errorCode != 0)
  alert(objData.parseError.reason);
  objStyle = new ActiveXObject("MSXML.DOMDocument");
  objStyle.async = false;

objStyle.load("Ingredients.xsl");

if (objStyle.parseError.errorCode != 0)
  alert(objStyle.parseError.reason);
  document.open("text/html","replace");
  document.write(objData.transformNode(objStyle));

Active Server Pages

Publishing live data to the Internet means that every time the web page is viewed, the data is fetched from the database. This has the advantage of the data always being up-to-date, but the downside is that it takes longer to set up and has a slightly longer loading time. We should also say that we are just touching the surface of ASP in this section. This is a massive subject, which is mostly outside the scope of this book. For an excellent study of Microsoft Access and ASP try Beginning ASP Databases from Wrox Press (ISBN 1-861002-72-6).

To use Active Server Pages (ASP) you will need either Microsoft Internet Information Server (IIS) or Microsoft Personal Web Server. IIS5 is available with Windows 2000 Server and Windows XP Professional. Microsoft Personal Web Server may be used for testing and development purposes only, if required. Many developers who have moved onto Windows 2000 and XP Professional operating systems have the advantage in that a full version of IIS is available for use when working with the web. That being the case, Personal Web Server may not be available on your machine and you will use IIS.

Just for information ASP is also available for Unix using a third party product. Details are available at http://www.sun.com/software/chilisoft/ . It has also been reported that Microsoft ASP.NET is being developed to also run on Unix-based web servers.

Try It Out-Creating ASP Files

1. In the Tables window of the example database, select tblIceCream

2. Select File Export from the main menu

3. From the Export dialog change the Save as type to Microsoft Active Server Pages

4. Save the file in a folder below your C:\Inetpub\wwwRoot directory (if this directory doesn't appear, you probably don't have IIS installed correctly - see the appendices)

5. Press the Export button to open the ASP Options dialog:

   

   Since the data is updated every time the ASP page is loaded, you need to fill in some details:

   Field	Description
   HTML Template	If you've got an HTML template you can enter its name here. This can help give your ASP pages a consistent look.
   Data Source Name	This can be an ODBC DSN (as shown) or ADO connection string. ADO is discussed in Chapter 21. This is how the ASP page connects to the Access database to get the data.
   User and Password	If you have security set on your Access database you should enter a user name and password here, otherwise you can leave them blank.
   Server URL	This allows you to enter the default URL of the web server that will host this page. Unlike HTML pages, which can be viewed directly by a browser, an ASP file must be processed by the web server before the browser can use it.

   After entering a DSN and the server URL shown in the above figure, you can view the web page by opening it in a web browser. In the case of this example, we have created a folder on the web server called BegVBA . The example file is placed in there. The URL localhost is the common URL used to specify the server running on the development machine. If you save the file in a folder other than BegVBA remember to use that folder name.

   Enter the URL: http://localhost/BegVBA/tblIceCream.asp

   

   This looks pretty similar to the previous static example, but this time the data will be fetched from the database every time the web page is viewed. In order to actually see the changes you will need to amend one of the records in the table. In this case, we have added some text to the Walnut Wonder record, but you can alter any record that you choose.

6. When you're done, hit the Refresh button in your browser. See how the changes you've just made are carried across.

   

Notice how the new data is visible immediately. You don't have to export this again, because each time this web page is viewed, the data is fetched directly from the database. Again for completeness a fragment of the ASP script taken from the above file is shown below. Note the use of the < % to indicate to the server that what follows is executable ASP code:

<%
If IsObject(Session("ice_conn")) Then
    Set conn = Session("ice_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "ice","",""
    Set Session("??ice_conn") = conn
End If
%>
<%
If IsObject(Session("tblCompany_rs")) Then
    Set rs = Session("tblCompany_rs")
Else
    sql = "SELECT * FROM [tblCompany]"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    If rs.eof Then
        rs.AddNew
    End If
    Set Session("tblCompany_rs") = rs
End If
%>