Exporting Access schema using XML

by Guest Contributor | Jul 02, 2003 7:00:00 AM

Tags: Susan Harkins, XML, schema, Microsoft Access

Takeaway: Often when you need two applications to cooperate most, compatibility problems emerge. Microsoft Access supports Extensible Markup Language (XML), which makes it easier to import and export data and schema information. Here's how it works.

When it comes to sharing, applications often act more like squabbling children than professional tools of the trade. Sometimes the solution is to find a third application that's compatible to both formats. Unfortunately, there isn’t always a compatible format available. The good news is that Microsoft Access supports Extensible Markup Language (XML), and XML almost effortlessly exports and imports data and schema information. That equates to a lot less work for you.

XML and Access
Access exports several XML file types. The three XML export options produce the following XML file types:

• Data (XML) produces an XML file.
• Schema of the Data produces an XSD file.
• Presentation of Your Data produces three files: an XML, an XSL, and an HTML file.

XML can share data, information on presenting that data, and schemas. In a relational database, schema refers to the tables. Specifically, schema identifies the fields and the relationships between fields and tables. Importing a schema can make creating a replica of a table much easier. In fact, you could quickly re-create the entire table structure using schema data.

You can export data or schema using the Access user interface or Visual Basic for Applications (VBA) code. To export schema manually:

1. Select a table in the Database window—for this example, we'll export the Customers (a table in Northwind, the sample database that comes with Access) schema.
2. Select Export from the File menu.
3. Name the XML file CustomersSchema.
4. Select XML as the file type in the Save As Type control. Don't type the XSD extension; XML will assign the right extension.
5. Click Export and Access will display three export options, as shown in Figure A: Data (XML), Schema Of The Data, and Presentation Of Your Data. The first two options are selected by default.
6. Choose Schema Of The Data; you're really just deselecting Data (XML).
7. Click the Advanced button. (You can omit details from the exported information, as shown in Figure B.) Don't make any changes right now, but you should know that you can omit primary key and index information from the exported file.
8. Click the Data tab and click OK.

You can learn a lot about an XSD schema file by viewing it in a browser. Figure C shows CustomerSchema.xsd in Word. The file is really just text that contains details about the Customers table structure.


Automating the Process with ExportXML
While it's easy to export schema manually, most likely you'll want to automate the process. To do so, use the ExportXML function in this form:
 
Application.ExportXML(objecttype, datasource[, datatarget][,
schematarget][, presentationtarget][, imagetarget][, encoding][,
otherflags])

All but the first two arguments are optional, but you'll need to include at least one of the target arguments. Table A defines the arguments. Table B lists constants for the objecttype argument. Table C lists values used by the otherflags argument.
Table A

Argument Explanation objecttype An intrinsic constant (see Table B) that defines the type of object exported. datasource Identifies the exported object by name. datatarget The path and file name for exported data (XML file). schematarget The path and file name for the exported schema (XSD file). presentationtarget The path and file name for the exported presentation information (XSL file). imagetarget The path and any exported graphics or image files. encoding Constants that determine text encoding: acUTF16 and acUTF8. otherflags A bit mask value that specifies a number of other behaviors (see Table C).

ExportXML arguments

 
Table B

Object Constant Data Access Page acExportDataAccessPage Form acExportForm Function acExportFunction Query acExportQuery Report acExportReport View acExportServerView Stored Procedure acExortStoredProcedure Table acExportTable

Object constants

 
Table C

Value Description 1 Embeds schema information with data in the document specified by the datatarget argument. 2 Excludes primary key and indexes from exported schema document. Comparable to the advanced option (see Figure B). 4 Creates an Active Server Page (ASP) wrapper when exporting reports. 8 Creates a live link to a remote SQL Server 2000 database when exporting reports. 16 Persists the exported object's ReportML file.

Otherflags values

The simple procedure in Listing A exports Access schema to an XSD file. Simply pass the procedure the necessary information—the name of the table you're exporting, the path (including the drive specification) that identifies where you want to store the exported file, and a name for the newly exported file—and the ExportXML function does the rest. The only glitch in the process is that you must identify the file as an XSD file in the schematarget argument. That's why the following line concatenates the XSD extension onto the path and target filename:
 
target = path & target & ".xsd"

Keep in mind that ExportXML will write over existing files (except when exporting a data access page object). The procedure doesn't include any error-handling, so you'll want to account for that possibility if you don't want to delete existing files. If the path you specify isn't a valid path, VBA will return error 2950.

After entering the procedure into a standard module, execute it once from the Immediate window to see how it works. For instance, if you wanted to export schema for the Customers table to a folder named Temp on your local system, your statement might resemble the following, where CustomersSchema is the name of the newly exported XSD file:
 
?ExptXML("Customers","C:\Temp\","CustomersSchema")

Be sure to include the final backslash character (\) in the path argument. You could also include error-handling to check for that character and add it if missing.

If the procedure is successful, it returns the value True. The resulting file will be exactly like the one you saw in Figure B.

Schema to go
Schema information is full of details about a table's structure and you might think exporting that information is difficult. To the contrary, using XML, you can quickly export the information to an XSD file—ready to import in any XML supporting application.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database and Web technologies. Her most recent books are SQL: Access to SQL Server by Apress, Mastering Dreamweaver MX Databases by Sybex, and Absolute Beginner's Guide to Microsoft Access 2002 by Que.

Print/View all Posts Comments on this article

Access Supports XML s_shanaz@... | 07/15/03

Re: Access Supports XML kallom | 10/29/03

Listing A weddins@... | 10/29/03

Broken Link eric@... | 10/29/03

Here's a little code snippet to get the needed info Tony Navarro | 10/30/03