Exporting of an actual Import/Export Specification

[Andy]

Bear with me on this explanation.

I have inherited an Access database with a multitude of import/export specs
for .txt files. I need to modify one of the import specs which has crica 250
fields in. Rather than re-writing the spec to put additional fields in the
middle of the spec (very time consuming with something this big), the ideal
thing would be to export the actual import/export spec to Excel, adjust it
there and then re-import to Excel.

I have been unable to do this and tried a number of ways. Selecting all
fields within the spec, CTRL+C and pasting into Excel works. When pasting
back into the spec Access box (CTRL+V) the spec seems to be fine. I then try
to 'Save As' and it all disappears.

Bottom line is, how I can export and import the specs into Excel and back
again and re-save them.

PLEASE HELP!!!

 

[John Nurick]

Hi Andy,

Import/export specifications are stored in a couple of hidden system tables
in the database, MSy­sIMEXSpecs and MSysIMEXColumns. After displaying them
(Tools|Options|View) you may be able to make the necessary changes simply by
running update queries on MSysIMEXColumns or even by editing the table
datasheet.

As always when messing with system tables, work on a spare copy of the
database.

 

[Andy]

Just the ticket. Would be a whole world of pain less if we were able to
import/export specs as is the case with virutally everything else.

Thanks for your help

 

[Ken Snell [MVP]]

You can import those specifications. From a new database, use File | Get
External Data... | Import menu. After selecting the file with the specs in
it, and the "import" window appears, click on the Options button at bottom
right of window. You'll then see an option to import the import/export
specs.

 

[Andy]

John, I might have spoken too soon. I am unable to insert additional rows to
the previously hidden 'MSysIMEXColumns.' table which holds the structure of
the spec. Have tried appending another table with the addition part to the
spec, but no joy either.

 

[John Nurick]

Andy,

I've never tried this in earnest, but in a test database just now I had
no difficulty inserting a row into MSysIMEXColumns (in datasheet view),
which then appeared as an additional field in the import spec in the
text import wizard.

If your database is secured, it may simply that the account you're
logged in with doesn't have sufficient permissions.

Also, when inserting records that correspond to additional fields in the
middle of the table you're importing, you presumably have to ensure that
the Start values of all subsequent fields are adjusted to the new
correct values.