Partilhar via

The New Import/Export Specification OM

  • Artigo

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.

Comments

  • Anonymous
    October 12, 2006
    I'm an energy manager and Access is invaluable at managing all of all my data. I use VBA extensively but don't know squat about XML. What's the meaning of the question mark in ? CurrentProject.ImportExportSpecifications.Item(0).XML and what are those angle brackets for? and what's xmlns? Is the Imex spec above automatically created or you have to build it yourself? And, if so, where does an Access aficionado get the knowledge to build the spec above? I suppose a general XML book doesn't teach you about AccessObject, FieldDelimiter, Columns PrimaryKey and the rest and how to put them together in the ensemble above. :-)

  • Anonymous
    October 12, 2006
    Hi grovelli, I'm happy you use Access :) You should keep in mind that all the XML goo that I mentioned above is not necessary if you only care about the UI. Even if you do want to automate everything programmatically, it isn't obvious that you do need to change the XML above. You can always use the UI to create the imex spec you want and then roll with it. I just exposed it so in case anybody wants to something more sophisticated/complicated with it (as in generating imex specs on the fly), which is something you couldn't really do with Access 2003. Let me address your issues:

  1. By "? CurrentProject.ImportExportSpecifications.Item(0).XML", I meant try running "? CurrentProject.ImportExportSpecifications.Item(0).XML" on your immediate window in VBE.
  2. You might want to read up on XML so you can grok the angle brakets, xmlns (XML namespace), etc. If you need a reference, let me know.
  3. The Imex spec above is auto-generated by Access. You can create one using the UI (in which you will never see the XML), and then use the VBE command I mentioned above to get it out and then tweak it (say, by changing the file name, etc).
  4. An XML book will not go into the detail of what AccessObject, FieldDelimiter means, as you noticed. But their meaning is quite straightforward to figure out as you play with the UI and export different Imex Specs. If you are really stuck I can try to go into more detail about them. take care, 3oF

  • Anonymous
    October 12, 2006
    Thank you very much Hugh, and what about the question marks in <?xml version="1.0"?> I'll try and manage imex specs with the UI for now, I think I'm already saturated with SQL, VBA, ADO and its extensions(on its way out?) DAO(resurrected?), DDL, you name it :-)

  • Anonymous
    October 12, 2006
    No problem. That is the XML declaration (http://www.w3.org/TR/2006/REC-xml-20060816/). It tells the XML parser which XML version we're using. The question mark shows it is a special kind of markup.

  • Anonymous
    October 12, 2006
    Thank you again. I'd forgotten macros to add to the list :-)

  • Anonymous
    October 12, 2006
    Hi, I work for a mailing house and we have 1000+ import specs set up in Access 2003. Thinking ahead will there be a way to convert these specs to the Access 2007 programmable specs? I suppose we could get the information from the system tables and then create them afresh. Will the old specs still work? Many Thanks Mark