The New Import/Export Specification OM

  • Article

In previous versions of Access (such as Access 2003), you can save the steps from an import or export operation into what we call Import/Export Specifications (Imex Specs). After saving it, you can always load it back so you don't have to customize the settings over and over again.

One point of contention, however, has been that it was a complex procedure to be able to create, delete, copy or change imex specs programmatically. That is a pity because Imex Specs are all about allowing import and export commands (such as using DoCmd.TransferText) to be automated.

For Access 2007, we are now exposing Imex Specs programmatically so you can create, delete, copy or change them at will. You will notice that now there is a new method off of the CurrentProject object: ImportExportSpecifications. This object is a collection where three particular methods are interesting to us:

  • CurrentProject.ImportExportSpecifications.Count - allows you to get the number of Imex Specs that this database currently has.
  • CurrentProject.ImportExportSpecifications.Item - gives you access to the ImportExportSpecification objects, who in turn expose the data in the specs:
    • Name - This obviously contains the name of the Imex Spec you specified when you saved it.
    • Execute - This method will run the Imex Spec.
    • Delete - This method allows the Imex Spec to be deleted.
    • Description - Contains the description text for the Imex Spec.
    • XML - This method will return an XML representation of the Imex Spec. For example:

? CurrentProject.ImportExportSpecifications.Item(0).XML

<?xml version="1.0"?>
<ImportExportSpecification Path="c:\temp\1.txt" xmlns="urn:www.microsoft.com/office/access/imexspec">
<ExportText TextFormat="Delimited" FirstRowHasNames="false" FieldDelimiter="," TextDelimiter="{DoubleQuote}" CodePage="1252" AccessObject="Table1" ObjectType="Table">
<DateFormat DateOrder="MDY" DateDelimiter="/" TimeDelimiter=":" FourYearDates="true" DatesLeadingZeros="false"/>
<NumberFormat DecimalSymbol="."/>
<Columns PrimaryKey="{Auto}">
<Column Name="Col1" FieldName="ID" Indexed="NO" SkipColumn="false" DataType="Long" Width="11"/>
</Columns>
</ExportText>
</ImportExportSpecification>

  • CurrentProject.ImportExportSpecifications.Add - This method allows you to create ImportExportSpecification objects. By giving it the name of the Imex Spec and the XML representation, one can create as many Imex Specs as wanted. You can also use this to copy Imex Specs:

CurrentProject.ImportExportSpecifications.Add "New", CurrentProject.ImportExportSpecifications.Item(0).XML

As you can see, Imex Specs are now easy to be created, deleted and changed (by making changes to the XML data). This should make automating import/export tasks even easier to be automated than they already were.

There is one caveat to all of this, though. The Imex Specifications exposed through the OM above (or created through it) are not the ones created in the Import/Export Wizard through the "Advanced" button (as in Access 2003 and earlier). There are new Import/Export Specifications in Access 2007 that are not backed by the MSysIMEXColumns and MSysIMEXSpecs tables. I'll talk about these in my next post.