Version 2.8 Approx 1.2 MB First Published 12 Nov 2019 Last Updated 17 July 2024
UPDATED 17 JULY 2024 - NEW VERSION
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.
This approach can still be used by clicking the Advanced button on the Import Text wizard. Import/export specifications are 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.
You can also create an Outlook task as a reminder to run a saved import or export task at a specified date/time:
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 sorting the tasks either by name or by date. 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.8 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, 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
4. I will be giving a presentation on this topic to the online Access Europe User Group on Wed 5 June 2024.
For further details, see Access Europe - June 2024
The session will be recorded and the YouTube video will be posted here later that month.
5. If there is sufficient interest, I could create an add-in version of this app. Please contact me to discuss if you would find this useful.
Related Article
See my article: Feature Request: Add Sorting & Editing to Import/Export Data Tasks
Version History
Version Date Release Notes
v1.0 12/09/2019 initial release
v2.2 11/01/2021 minor updates
v2.3 03/12/2023 update with fixes to bugs reported by Danick Sieras at AWF
v2.6 29/03/2024 additional functionality - new forms to edit old & new style specifications
v2.7 09/06/2024 bug fix to path in ModifyDataTask procedure
v2.8 16/07/2024 corrected error in SpecType field info
Video
I demonstrated this app as part of my presentation to the Europe chapter of AccessUserGroups.org on 5 June 2024.
The video is now available on YouTube at https://youtu.be/jUed-4aCUxs or you can click below:
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 17 July 2024
Return to Example Databases Page
Return to Top