Example Apps for Businesses, Schools & Developers

Version 2.3           Approx 0.8 MB                 First Published 12 Nov 2019                 Last Updated 5 Dec 2023

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 2007, import/export (IMEX) specifications were saved in 2 system tables : MSysIMEXSpecs / MSysIMEXColumns.
Import/export specifications were only available for text files e.g. CSV.

This approach was supplemented with a wizard driven & arguably more powerful  XML based system of Import/Export Data Tasks in Access 2007.
The new approach is very simple to use and works for a wider range of files including Excel spreadsheets and Access tables.
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:

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


The Access help file contains the following information:

IMEXSpecObjectMembers

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:

IMEXSpecMainForm
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:
a)   All saved IMEX data tasks (created in Access 2007 or later)
b)   Any 'traditional/old-style IMEX specifications


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

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

3.   It is still possible to create the 'traditional' IMEX specification on TEXT files.
      To do so, click 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
      In these cases, the only way to save the IMEX specification is by saving as a data task



Download

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



Related Article

See my article: Feature Request: Add Sorting & Editing to Import/Export Data Tasks



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Do let me know if you find any bugs in the application.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                       Mendip Data Systems                       Last Updated 5 Dec 2023



Return to Example Databases Page




Return to Top