View & Edit IMEX Data Task Specifications (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
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:
Code:
<?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/forums/showthread.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
 

Attachments

  • CheckIMEXSpecifications.zip
    37.7 KB · Views: 849
  • IMEXSpecObjectMembers.PNG
    IMEXSpecObjectMembers.PNG
    42.2 KB · Views: 2,507
Last edited:

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
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.
 

Attachments

  • CheckIMEXSpecifications_v2.zip
    69.4 KB · Views: 733
  • MainForm.PNG
    MainForm.PNG
    38.6 KB · Views: 2,337
Last edited:

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
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.

EDIT: 11 Jan 2021 - Just discovered a bug in the attached file. Please download the fixed version 2.2 in the next post instead.
 

Attachments

  • CheckIMEXSpecifications_v2.1.zip
    79.9 KB · Views: 780
Last edited:

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
Version 2.2 attached which fixes the bug causing error 31602 in the previous version.
No other changes made
 

Attachments

  • CheckIMEXSpecifications_v2.2.zip
    84.8 KB · Views: 667
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom