Does anyone know how to modify an existing import specification in Microsoft Access 2007? In older versions there used to be an Advanced button presented during the import wizard that allowed you to select and edit an existing specification. I no longer see this feature but hope that it still exists and has just been moved somewhere else.
A:
I don't believe there is a direct supported way. However, if you are desparate, then under navigation options, select to show system objects. Then in your table list, system tables will appear. Two tables are of interest here: MSysIMEXspecs and MSysIMEXColumns. You'll be able edit import and export information. Good luck!
Knox
2008-09-27 12:07:01
Those tables are not editable by the user. They can only be edited through Jet.
David-W-Fenton
2008-09-27 22:54:10
David, I can edit both of the MSysIMEXColumns and MSysIMExSpecs table for naming fields and changing types. Not extensively tested by me, and obviously unsupported, but they ARE editable.
Knox
2008-09-28 00:18:48
This must be something that has changed in A2K7, as I have no version of Access that allows me to directly edit them (A97, A2K2, A2K3).
David-W-Fenton
2008-09-29 23:34:21
You *can* edit the MSysIMEXColumns and MSysIMEXSpecs tables with queries in Access 2007 and earlier versions. Try something like this:UPDATE MSysIMEXColumnsSET FieldName = "LastName"WHERE FieldName = "Field4" AND SpecID = 5
Chris OC
2008-10-01 03:29:37
@Knox: Nice trick !
iDevlop
2010-06-07 10:50:22
+1
A:
I am able to use this feature on my machine using MS Access 2007.
On the Ribbon, select External Data
Select the "Text File" option
This displays the Get External Data Wizard
Specify the location of the file you wish to import
Click OK
This displays the "Import Text Wizard"
On the bottom of this dialog screen is the Advanced button you referenced
Clicking on this button should display the Import Specification screen and allow you to select and modify an existing import spec.
For what its worth, I'm using Access 2007 SP1
Tim Lentine
2008-09-29 16:14:19
A:
Tim Lentine's answer seems to be true even in the full release. There is just one other thing I would like to mention.
If you complete your import without going into "Advanced..." and saving the spec, but you do save the import for reuse at the end of the wizard (new feature AFAIK), you will not be able to go back and edit that spec. It is built into the "Saved Import". This may be what Knox was referring to.
You can, however, do a partial work around: 1. Import a new file (or the same one all over again) but, 2. This time choose to append, instead of making a new table. 3. Click OK. 4. Go into "advanced" All your column heading and data-types will be there. 5. Now you can make the changes you need and save the spec inside that dialog. Then cancel out of that import (that is not what you wanted anyway, right?) 6. You can then use that spec for any further imports. It's not a full solution, but saves some of the work.
A:
David, I can edit both of the MSysIMEXColumns and MSysIMExSpecs table for naming fields and changing types. Not extensively tested by me, and obviously unsupported, but they ARE editable. – Knox Sep 28 '08 at 0:18
This works best.
longbowz
2010-07-06 07:09:13
I would definitely advise against it. Those are system tables and you should leave them alone in my opinion, and edit them only through the interfaces provided by Access.
David-W-Fenton
2010-07-06 22:41:42