Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-12-2019, 10:09 AM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,381
Thanks: 115
Thanked 3,112 Times in 2,830 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
View & Edit IMEX Data Task Specifications

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...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:
• 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
Attached Images
File Type: png IMEXSpecObjectMembers.PNG (42.2 KB, 149 views)
Attached Files
File Type: zip CheckIMEXSpecifications.zip (37.7 KB, 12 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-15-2019 at 03:28 AM.
isladogs is offline   Reply With Quote
The Following 3 Users Say Thank You to isladogs For This Useful Post:
Gasman (11-12-2019), MrHans (11-12-2019), NauticalGent (11-14-2019)
Old 11-14-2019, 02:00 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,381
Thanks: 115
Thanked 3,112 Times in 2,830 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: View & Edit IMEX Data Task Specifications

I've moved the replies to this thread to the original thread https://access-programmers.co.uk/for...17#post1650617 to make it easier to reply without the need for moderation
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-14-2019, 02:18 PM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,381
Thanks: 115
Thanked 3,112 Times in 2,830 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: View & Edit IMEX Data Task Specifications

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.
Attached Images
File Type: png MainForm.PNG (38.6 KB, 55 views)
Attached Files
File Type: zip CheckIMEXSpecifications_v2.zip (69.4 KB, 10 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-15-2019 at 04:59 AM. Reason: Change to final sentence to add the system table and query. Updated screenshot
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
Gasman (11-14-2019), jdraw (11-14-2019)
Old 11-15-2019, 04:57 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,381
Thanks: 115
Thanked 3,112 Times in 2,830 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: View & Edit IMEX Data Task Specifications

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.
Attached Files
File Type: zip CheckIMEXSpecifications_v2.1.zip (79.9 KB, 5 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Reply

Tags
import/export data tasks , xml

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
view import specifications Droman General 4 10-30-2012 01:48 AM
Switch Between View and Edit Mode yus786 Forms 6 06-27-2008 07:19 PM
Edit Form in Datasheeet View depawl Forms 11 07-29-2007 03:27 AM
Import delimited data specifications Eric Gilchurch General 4 02-21-2007 05:21 PM
edit button for tabular view chinkygogo Macros 1 08-23-2006 04:46 AM




All times are GMT -8. The time now is 02:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World