Import/Export Data Tasks

[isladogs]

I'm going to have to disagree with that assessment. They are one of those "improvements" are are designed to shelter people from details. If you can't get to the details, you can't change anything including the source file name or location. You also can't use the same spec for multiple imports as you can with the earlier method. I have a process that both imports and exports the same file type and I use the same spec whether I am importing or exporting. The new, "improved" method can't do that. At my last client, I had to convert from the new to the old at least a dozen times for different people. They frequently made little databases that they shared among themselves. However, what would run on one person's PC wouldn't run on another's since the file paths were never the same. So I had to change all the import/export actions to use TransferText or TransferSpreadsheet so the source/target could be controlled by the user.

Hi Pat
Only just noticed your post. Whilst I agree with much of what you've written, for end users the new data tasks are much easier to work with. The problems are indeed the lack of transparency and inability to edit the tasks.
However, I suggest you look at my new item in the code repository, I've provided code which allows you to view and edit the XML in the new data tasks. See https://www.access-programmers.co.uk/forums/showthread.php?t=307897

Perfect for when an app is distributed and the external files are located in a different place.

 

[isladogs]

Re: View & Edit IMEX Data Task Specifications

I wasn't suggesting making the code accessible to the standard user either.

As for the other points, do remember that data tasks largely replaced the old method in A2007. Whilst the old import / export specifications will still run in newer versions, you cannot create 'traditional' IMEX specs for e.g. Excel files and save them to the two legacy MSys tables.

CORRECTION (15 Nov 2019):
You can still create IMEX specs for importing TEXT files in more recent versions of Access but not for Excel files/Access tables

 

[isladogs]

I've just updated the example app 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 new style saved data tasks (A2007 or later)
d) added another report to view details of any 'traditional' IMEX specifications saved in the 2 MSys tables

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.
The form/code should never be accessible to standard users.
Using a form also means that this can be used with ACCDE files.

The new version has now been added to the code repository
https://www.access-programmers.co.uk/forums/showthread.php?t=307897

 

[isladogs]

Pat
I understand totally why you prefer the old 'traditional' IMEX specification method which was I believe introduced in A2003.

In fact the 2 MSys tables are created automatically in newer versions when you first run an import/export whether or not you save it as an IMEX specification.

However, whilst you can certainly use any existing IMEX spec saved in the 2 MSys tables in A2007 or later, the ability to create a new 'traditional' IMEX spec is limited.

You can still do so for TEXT files by clicking the Advanced button then Save As ... on the Specification window.

However if you import Excel files or tables from another database the Advanced option does NOT appear. Therefore AFAIK, you cannot therefore save it as an OLD style IMEX specification. If you need to save it, you have to use the newer data tasks whether you like them or not.

 

[Guus2005]

Where are the IMEXSpecs stored if not in the MSysIMEXSpecs/Columns tables?

ImportExportSpecifications seems to be read only. How are they set, using the wizard perhaps?

 

[isladogs]

The 'old' IMEX specifications were stored in the two MSysIMEX system tables
The 'new' IMEX data tasks introduced with Access 2010 are stored in XML as part of the Access file.

The data tasks are those created with the IMEX wizards and as MS doesn't provide any method of editing them, they are 'effectively read only'
However, although the method is slightly obscure, it is possible to edit these using VBA as demonstrated in my example app: View & Edit IMEX Data Task Specifications | Access World Forums (access-programmers.co.uk) which I believe you have since found.

NOTE: It is still possible to specify the old MSys tables approach for TEXT files only as explained in the other thread

 

[NauticalGent]

Here is something I put together while I was importing from an Outlook folder...

Changing Saved Import XML

The need for Access and Outlook to talk to each other is inevitable. There are plenty examples on the web if all you are doing is working with your own e-mail account, but when you need to work with Public Folders, the examples get increasingly difficult to find. Hopefully, this post will make...

www.access-programmers.co.uk