Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Example Apps for Businesses, Schools & Personal Use

View & Edit Import/Export (IMEX) Data Task Specifications

Click the image to view a larger version ...

Return to Example Databases Page

 

    <?xml version="1.0" encoding="utf-8" ?>

   <ImportExportSpecification Path = "G:\MyFiles\ExampleDatabases\IMEXSpecifications\Test.txt"

            xmlns="urn:www.microsoft.com/office/access/imexspec">

            <ImportText TextFormat="Delimited" FirstRowHasNames="false" FieldDelimiter=","  

                        TextDelimiter="" CodePage="850" Destination="Test" >

                       <DateFormat DateOrder="DMY" DateDelimiter="/" TimeDelimiter=":"

                                  FourYearDates="true" DatesLeadingZeros="false" />

                       <NumberFormat DecimalSymbol="." />

                       <Columns PrimaryKey="{Auto}">

                                <Column Name="Col1" FieldName="Field1" Indexed="NO" SkipColumn="false"      

                                           DataType="Text" Width="84" />

                       </Columns>

            </ImportText>

    </ImportExportSpecification>

IMEXSpecObjectMembers IMEXSpecMainForm

I would like to thank fellow AWF members jdraw & the DBGuy for prompting me with starter code.

Thanks also to smig for suggesting a procedure that I adapted.

Prior to Access 2010, import/export (IMEX) specifications were saved in 2 system tables : MSysIMEXSpecs / MSysIMEXColumns.

 

This system was replaced with a wizard driven & more powerful  XML based system of Import/Export Data Tasks in Access 2010. However, ease of use has also led to greater obscurity in how it works.

 

For example, the contents of a saved Data Task cannot easily be viewed or edited

This is an example of the XML for an Import task:

The Access help file contains the following information:

The attached example database includes a module modIMEX containing code to do all of the following:

 

•   View Import Export Specifications - this saves all data task specifications to a user defined

    system table (normally hidden) called USysIMEXSpecs

 

•   Execute Data Task / Run Data Task – two different ways of running a task using VBA

 

•   Edit Data Task – replace a specified section of the XML with different text string

 

•   Modify Data Task Path – replace the existing path of the external file with a new path

   NOTE: you do NOT need to know the existing path to use this

 

•   Modify Data Task Destination – replace the existing destination table for an import task with

    a new table name. Once again the existing value is NOT needed

 

•   Rename Data Task – ‘does what it says on the tin’

 

•   Delete Data Task – delete an existing data task using VBA

 

 

The example database has 2 import & 2 export data tasks for use with a sample file test.txt

Store this text file (supplied) in the same folder as the sample database or any folder of your choice.

 

However, the four IMEX tasks will not run unless your path is the same as mine – VERY UNLIKELY!

Using the above procedures you should easily be able to edit the XML to make these work for you.

 

All these procedures can be run from the main form

Click the image to view a larger version ...

The main purpose of the form is so that it can be used by program admins where appropriate to modify saved data tasks when an application is deployed.

 

Using a form eans that this functionality can be used with compiled ACCDE files as well as ACCDB files.

 

When the form is loaded, the hidden system table USysIMEXSpec is automatically created (using DDL) if it doesn't exist.

 

The application also contains two reports to view details of:

1. All saved IMEX data tasks (Access 2010 or later)

2. Any 'traditional/old-style IMEX specifications

 

NOTE:

It should be stressed that standard users should NEVER have access to the code used or the form.

To use this in your own applications, you only need to import the form frmManageTasks, the two reports and the module modIMEX.

 

 

Click to download:       CheckIMEXSpecifications v2.2                Approx 0.8 MB (zipped)

NOTE (updated 15 Nov 2019):

It is still possible to create the 'traditional' IMEX specification on TEXT files by clicking the Advanced button on the IMEX wizard then clicking Save As on the next window.

 

However the Advanced button doesn't appear when working with Excel files or Access tables so the only way to save the IMEX specification is by saving as a data task