Cannot save changes to IMPORT SPECIFICATION

[dusanv]

I have an import specification for importing a fixed width text file. The record layout of this file changed and I'm trying to update the import specification.

I am able to edit the specification (add and rearrange fields), however, when I hit the save button all my modifications are discarded and the specification reverts to the original form.

Has anyone run into this problem before? Any insight on this will be greatly appreciated.

 

[johnnymac43]

Is it possible that you do not have permission to change an import specification?

To check, do the following (Access 2000):
> Tools, Options, View Tab, Tick Show System objects, OK.
> Tools, Security, User and group permissions, Permissions tab, Object type - Table, MSysIMEXSpecs.

HTH

John

 

[RickSpr]

Actually, I expect you really are changing the import specification--it's just being saved with a numeric suffix attached to it.

I don't have the logic of it all worked out, but I've observed it before. Apparently, if an import spec was built by Access, Access won't let you make changes to it. When you try, it appends a digit to the name before it saves it.

If you set the option to show system tables, as John suggested, I'll bet you'll see two or more specs with the same name, except for the numeric suffix.

To fix the problem, try deleting all the similarly named specs and running the import wizard again. If there are a lot of fields, you might want to first bring up the import spec dialog to show the fields, and do screen prints to capture the information.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dusanv]

Thanks for your replies.

johnnymac43: I checked the permissions for MSysIMEXColumns and all changes were allowed.

RickSpr: I didn't observe that numeric suffix is added to the specification name once edited and then saved, but I did observe that SpecID value in MSysIMEXSpecs table increases by 1.

I hacked my way to directly modifying the MSysIMEXColumns table and that did the trick for me but I'd still like to find a way to successfully use the specification wizard.

 

[RickSpr]

Regarding the suffix: Either I misremembered, or the technique used has changed from Access 97 to Access 2000. I'm sure I've seen &quot;<tablename>_LinkSpecification1&quot;, etc.

Just on a hunch: Did you actually load the spec before you made your changes? When you first enter the spec dialog, there's no spec ID associated with the field specs, so when you save it the wizard treats it like a new spec. But if you open the spec first (even though it just loads the same details again), it might save it under the same ID.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dusanv]

Rick, this is what happens:

I open an existing import specification via the wizard. Then I do my edits. Then I hit &quot;Save As&quot;. As soon as I do that, you can see Access &quot;scrolling&quot; through the import spec fields and when it's done, my edits are gone! At this point, I can save the original spec either under the same or new name but, irregardless, all my work was in vain.

Dusan

 

[RickSpr]

Gotcha. I don't think I've ever seen that. I guess all my text wizard stuff has been in A97.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein