Version 2.2           Updated 10 Jan 2021           Approx 0.8 MB

I originally wrote the attached utility in response to a question about editing external files from Access by Access World Forums member smig in this thread:
Editing an external text file - is it possible?

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 & arguably 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 text 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 database path is the same as mine – VERY UNLIKELY!
You should easily be able to edit the XML using the above procedures to make these work for you.

All these procedures can be run from the main form

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 means 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.

You will also need to add the Microsoft Office xx.0 reference library to your application.

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



Colin Riddington           Mendip Data Systems                 Last Updated 10 Jan 2021