How to edit a saved Import/Export spec in 2007 (1 Viewer)

Might have asked this question in another way but essentially I have a saved Export spec and I want to be able to see what it is doing ie edit it. However I only get the chance to run or delete it.

It is creating a pdf report which is what I wanted but I need to check its underlying recordsource.

Any suggestions?

1. The export spec doesn't have anything to do with the recordsource.

2. A SAVED EXPORT includes the export spec as well as recordsource information.

In 2007 I don't know of any way to edit a saved export or saved import. I think the only way is to delete it and then recreate it (bummer I know). I hope they got that fixed in 2010 but I haven't done any of those yet in 2010 so I don't know if they have. That seems to be a very stupid thing to do - save the export (or import) but not have any way to edit it.

The most effective way around it is to make your own import system. After making my own I have abandonned the Access import specs entirely. A roll your own import system can be far more powerful.

I created a VBA module to create recordsets and import text files into them. All the specs are held in a table with fields for the ImportID, FieldID, FieldName, StartPosition, FieldLength and FieldFormat.

Just a matter of parsing the imported file in a loop using these specs. All kinds of extra features to contol which lines are imported can be easily added to your own system.

V-Tools has an import-export specification editor.

http://www.skrol29.com/us/vtools.php

Save a couple of test Import/Export specs. Then...

Explore these commands in the immedate winidow and then refer to the object browser for a place to start:

? access.CurrentProject.ImportExportSpecifications.count
? access.CurrentProject.ImportExportSpecifications(0).name
? access.CurrentProject.ImportExportSpecifications(0).description
? access.CurrentProject.ImportExportSpecifications(0).xml

You can change the name and description but you must progamatically delete an existing definition (xml spec) and then replace it with a new one, modified to your needs.

You will need to create the spcification as a well formed xml string.

Good luck...

if you manually go through the import steps, at the time you pick the import spec, at that point you can bring it up, and edit it.

you certainly can in A2003.

gemma-the-husky said:

you certainly can in A2003.

Click to expand...

Have you tried it? Can you insert rows? Can you move rows?

This is one of the best threads I've found on the specifics. I'll add here what I've compiled on the matter for A2003. Hope I don't retread too much old ground.

utteraccess.com/forum/Edit-Import-specification-t1950181.html gives an overview expressing that it's a huge pain to edit specs in 2003.

skrol29.com/us/vtools.php purports to address this; I haven't run it. Freeware. He leaves source code viewable; he has an extensive amount of code dealing with specs in Access 03. See the routines beginning with p_Spec_.

CurrentProject.ImportExportSpecifications.Add name_of_spec, xml is demonstrated in mrexcel.com/forum/showthread.php?t=615680 where it purports to build an import spec, supposedly for A2007 and later.

You might try working with schema.ini (use with TransferText) as detailed in support.microsoft.com/default.aspx?scid=kb;en-us;155512 Note it is only "certified" for Access 97 (but I can't imagine it's not still usable).

The closest I've [seemed to] come to inserting lines is appending sufficient 'dummy' lines at the bottom, Ctrl-A, paste to Excel, modify, Ctrl-A, Ctrl-V back to Access. This seems to work, but most gruesomely. You get 5 error message boxes that you bite your lip and ignore; and maybe have to delete a duplicated first record field; and delete an autonumber key field that the Easter bunny puts in the created table. This sounds absurd, I know; but once you get confidence in it, it's awfully nice to make changes in Excel vs. the clunker interface (unsizable, too, gee thanks) in the Access wizard.

Not a slam dunk in any way. Galaxiom, FYI I also roll my own, with code using tableDef and .CreateField, .Size and .Type.

Sorry for lame links; you need 10 posts on this board to enter formed URLS.

Best method for a text import is create a ????.txt file in a folder.
Then within MS Access import this txt file. During the first step of import process - Click the "advanced " button. This will bring up an import specification screen. Within this (Using either "Delimited" or "Fixed" setting ) you can create fields and definitions of the data (Field Name; Data type; Skip/Don't Skip). If you want to save or modify the specification use the "Spec" button to reload a previous spec for edit or "SaveAs " button to save a spec name. Once done there is no need to complete the import/export - simply Click Cancel.
Also you can simply use the file / export method and follow above steps.

The file layout for the specification is held in Hidden System Files and called MSysIMEXSpecs.