Version 2.5           Approx 1.2 MB                 First Published 12 Nov 2019                 Last Updated 29 Mar 2024


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.
The wizard also does not provide any means of renaming or sorting the tasks. This can be a problem if you have a lot of saved tasks.

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:


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

•   View Import Export Data Tasks - this saves details of all saved data tasks to a user defined system table (normally hidden) called USysIMEXSpecs
    It also opens a report rptUSysIMEXSpec giving details of each saved data task.

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



Download

Click to download:       ManageIMEXSpecifications v2.5                Approx 1.2 MB (zipped)



Example App

The main purpose of the app is for use by program admins where appropriate to modify saved data tasks when an application is deployed.

All interaction is done using a form. This means that it can be used with compiled ACCDE files as well as ACCDB files.

The example database opens to a startup form which gives a summary of its purpose:


Click Continue to open the main form used to manage data tasks


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.

When the form opens, it catalogues the details of all saved data tasks to a hidden system table USysIMEXSpecs
The table is automatically created (using DDL) if it doesn't exist.

Click the View NEW IMEX Data Tasks Report button to view detals of all saved data tasks


Use the combo box to select any of the saved IMEX data tasks. Click the View button alongside to view the details of that task in a form


NOTE: You can copy the XML to the clipboard for pasting into another document

None of the four IMEX tasks included with the app will run unless your database path is the same as mine – VERY UNLIKELY!
However, you should easily be able to edit the XML using the above procedures to make these work for you.

For example, you can modify the external file path or the destination then execute the task

NOTE:
When running an import task on a regular basis, you can use the same data task provided the file to be imported always has the same name and file path.
However, to do so, you need to know both of those details. This utility allows you to specify those details using an existing data task



The form also allows you to view details of any 'old style' IMEX specifications saved to the 2 system tables MSysIMEXSpecs and MSysIMEXColumns

Click the View OLD IMEX Specifications Report button to view these in a report


Use the combo box to select any of the saved IMEX specifications.


Click the View button alongside to view the details of that IMEX specification in a form





NOTE:
1.   The app is ONLY intended for use by programs admins.
      Standard users should NEVER need access to the code used or even the form.

2.   To use this in your own applications, you need to import the following objects:
      •   4 forms frmManageTasks, frmUSysIMEXSpecs, frmMSysIMEXSpecs, fsubMYsysIMEXColumns
      •   3 reports rptUysIMEXSpecs, rptMSysIMEXSpecs, rsubMSysIMEXColumns
      •   2 modules modIMEX and modFunctions.

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

3.   If preferred, you can create a 'traditional' IMEX specification, but ONLY for TEXT files.
      To do so, click the Advanced button on the IMEX wizard then clicking Save As on the next window.

      The Advanced button doesn't appear when working with other file types such as Excel spreadsheets or Access tables
      In these cases, the only way to save the IMEX specification is by saving as a data task



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 29 Mar 2024

Return to Example Databases Page

Return to Top