ACC2002: How to Use the "Live Data" Option When You Export to XML (304172)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304172
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SUMMARY

When you export a form or a report, you can export live data if you are using a Microsoft Access project (ADP) that is connected to Microsoft SQL Server 2000 or later. Live data is not updatable. However, if you refresh the page by closing and reopening the browser, the server is queried for a new recordset. The new recordset then reflects any changes that have been made since the last time that you accessed the page. The SQL Server must be a Web-enabled SQL Server, and it must be properly configured for XML support.

MORE INFORMATION

The following example assumes that you have a properly configured Web server. It also assumes that the Northwind sample database is installed on the SQL Server.

Before you start to configure the SQL server, create a folder under C:\Inetpub\wwwroot that will be your virtual directory. You must also create two subfolders under the virtual directory. Name these subfolders template and schema. In this example, the virtual directory is named Northwind.

Configuring SQL Server for XML Support

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Configure SQL XML Support in IIS. This opens the "IIS Virtual Directory Management for SQL Server" window.
  2. Expand the server tree, and then select the Web site that you want to configure. On the Action menu, click New, and then click Virtual Directory to display the property page. On the General tab, enter a virtual directory name that is the same as the SQL Server database name, and then browse to the folder that you created in the wwwroot subdirectoy.
  3. On the Security tab of the dialog box, specify the SQL Server authentication method, and then enter the logon credentials if you must do so.
  4. On the Data Source tab, enter the name of the SQL Server that you want to configure. If the SQL Server is installed as an instance, enter the full instance name. Click Northwind as the name of the database.

    NOTE: When you are exporting live data from Access, you must specify the SQL Server database name. You cannot use (local). During the export operation, the SQL Server name is passed for use in retrieving data. If you use (local), the query will not be successful.
  5. On the Settings tab, click the Allow URL queries, the Allow template queries, the Allow Xpath option, and the Allow POST option.
  6. On the Virtual Names tab of the dialog box, click New to create the virtual name of the template type.
  7. In the Virtual Name Configuration dialog box, enter Template for the virtual name, click Template for the type, and then enter the path to your template subdirectory in the Northwind folder.
  8. After you save the template virtual name, click New on the Virtual Names tab to create a virtual name for schema. Enter Schema for the Virtual name, click Schema for the type, and then enter the path to your schema subdirectory in the Northwind folder.
  9. After you save the template virtual name, click New again on the Virtual Names tab to create a virtual name for the schema. Enter dbobject for the Virtual name, click dbobject for the type, and then save the virtual name.
These steps create a virtual directory Northwind. By default, the queries that are specified using this virtual directory are executed against the database. It a good idea to test the virtual directory by executing the following statement in your browser: 
http://MyServer/northwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
This returns an XML document of the Employees table in the Northwind database on the SQL Server that you have configured. You are now ready to export XML as live data from an Access project that is connected to Northwind.


Exporting Live Data in XML from an Access Project

The following steps export a form that is based on the Customers table from an Access project that is connected to the Northwind SQL Server database.
  1. Start Microsoft Access, and then create a new project against the Northwind SQL Server database on the server that you have configured for XML support. Make sure that the actual SQL Server name is specified in the connection properties. Do not use (local).
  2. Use the Auto Form: Columnar Wizard to create a new form that is based on the Customers table. Save the form as ExportCustomers.
  3. Export the form to XML format. Include the schema.
  4. Click Advanced in the Export XML dialog box.
  5. On the Data tab of the Export XML dialog box, click Live Data.
  6. Save the form in your wwwroot folder.
  7. Click OK to export the form.
  8. Open the resulting .htm file in your browser.

    NOTE: For live data to work successfully, you must select an HTML wrapper for the presentation. ASP wrappers do not work. They return a blank page in the browser. No error message is returned.

REFERENCES

For additional information about configuring SQL XML Support in IIS, see SQL Server Books Online, which is available at the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b

Modification Type:MajorLast Reviewed:5/5/2006
Keywords:kbhowto KB304172
©2004 Microsoft Corporation. All rights reserved.