View & Edit IMEX Data Task Specifications

[isladogs]

Prior to Access 2007, import/import specifications were saved in 2 system tables : MSysIMEXSpecs / MSysIMEXColumns

In Access 2007, this system was largely replaced (but see note below) with a wizard driven & arguably more powerful XML based system of Import/Export Data Tasks.
However, ease of use has also led to greater obscurity in how it works.

Unfortunately, the contents of a saved Data Task cannot easily be viewed or edited. It is often easier to delete a task and start again if it doesn't run.

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

<?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>

As a recent thread https://www.access-programmers.co.uk...d.php?t=307860 developed, the need for code to do both of these things became evident.
I've now created code to do exactly that.

Many thanks to jdraw & the DBGuy for prompting me with starter code and to smig for suggesting a procedure that I adapted.

The Access help file includes the following info:


The attached database includes a module modIMEX containing code to do the following:
• ViewImportExportSpecifications – saves all data tasks specifications to a user defined system table (normally hidden) called USysIMEXSpecs
• ExecuteDataTask / RunDataTask – two different ways of running a task using VBA
• EditDataTask – replace a specified section of the XML with a different text string
• ModifyDataTaskPath – replace the existing path of the external file with your own path. You do NOT need to know the existing path to use this
• ModifyDataTaskDestination – replace the existing destination table for an import task with your own table name. Once again the existing value isn’t needed
• RenameDataTask – ‘does what it says on the tin’

The zip file contains database has 2 import & 2 export data tasks for use with a sample text file test.txt. Store this in the same folder as the sample database or any folder of your choice

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

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

To use this code in your own applications import the module modIMEX and (optionally) the system table USysIMEXSpecs

Hope this helps some of you

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 specification is by saving as a data task

[isladogs]

I've moved the replies to this thread to the original thread https://access-programmers.co.uk/for...17#post1650617 to make it easier to reply without the need for moderation

[isladogs]

I've updated the application as follows:
a) added code to allow data tasks to be deleted
b) changed the app so all functions can now be run using a form
c) added a report to view details of all saved data tasks (A2007 or later)
d) added another report to view details of any 'traditional / old-style' IMEX specifications



The purpose of the form is so that it can be used by program admins as appropriate to modify saved data tasks when an application is deployed.
Using a form also means that this can be used with ACCDE files.

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

To use this with your own applications, import the hidden user defined system table USysIMEXSpec, the form frmManageTasks, query qryMSYsIMEXSpecs, both reports and the module modIMEX.

[isladogs]

Further minor updates to v2.1.
a) When the form is loaded, the hidden system table USysIMEXSpec is now automatically created (using DDL) if it doesn't exist.
b) The query qryMSYsIMEXSpecs is no longer used

This means you now only need to import the form frmManageTasks, the two reports and the module modIMEX to your own applications.