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="." />
<Column Name="Col1" FieldName="Field1" Indexed="NO" SkipColumn="false" DataType="Text" Width="84" />
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:
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
replace a specified section of the XML with a different text string
replace the existing path of the external file with your own path. You do NOT need to know the existing path to use this
replace the existing destination table for an import task with your own table name. Once again the existing value isnt needed
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