Import/Export Data Tasks

[Gasman]

Re: View & Edit IMEX Data Task Specifications

Glad you have a solution for line numbers though still not clear why it matters.
However just to clarify, in A2007, am I correct that the old system applies where import/export specs are saved to the two MSysIMEX tables

Yes, entries are in there. I have a few specs I use in my SSAFA DB.
I used your code to populate your Usys table and reviewed the result which looks good.

I do not use specs that much, but no doubt it will come in handy in the future.

Personal preference on the line numbers, mainly as they are not required.?

Thanks again.

 

[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.

 

[Pat Hartman]

Re: View & Edit IMEX Data Task Specifications

Good job as usual Colin but I probably wouldn't make this procedure accessible to the user. Whenever you need to make changes to the import/export spec, they should be made to the master copy of the FE. Otherwise, changes made to a user FE will be lost when a new version of the FE is released.

For applications used by others, I prefer the old style method that uses TransferText. It is quite easy to provide an interface that substitutes a custom file name without making any change to the spec itself. In fact, most of my exports use custom file names. Some part is fixed and then date and/or some additional information is concatenated so that each export file name is different and doesn't overlay earlier exports unless you export the same data for the same time period. So exporting the transaction data for customerX for October 2019 would always generate the same file name and newer versions should overlay older versions. Imports may or may not be consistent since typically they are created by applications outside of our control. I have some where the filename is always the same and others where the filename includes data such as a bank account number and a statement date.

 

[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

 

[Pat Hartman]

you cannot create new IMEX specs and save them to the two legacy MSys tables. In fact the MSys tables are no longer created by default.

I don't have any trouble in A2016. I never use the new methods because I believe them to be less useful. These tables were never created by default. They are only created if you actually save a spec.

 

[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