Editing an Import Specification

[Don]

I must be missing something obvious here, but I just can't seem to find a
way to edit an Import Specification. Basically I just need to add a field
between two existing fields.

Any help will be appreciated!

Thanks!


Don

 

[Ken Snell [MVP]]

You should be able to edit a specification while doing an import of a file
that matches the new specification.

 

[George Nicholson]

Start to do the import manually. There is an "Advanced" button within the
import wizard that should allow you to load ("Specs..."), modify & then
re-save an existing specification.

AFAIK, you are *not* required to finish the import. As long as you re-save
the spec before canceling the wizard, your spec has been edited.

HTH,

 

[Don]

George & Ken,

The problem I am having is in "editing" the spec. It does not seem to allow
a field to be inserted. Just manually copy the field description
information from row to the next. If I go far enough in the process, it
recognizes there is a mismatch between the spec and the new file (delimited
text) and simply adds a new field to the end of the spec list. I have not
found a way to "move" this field up the spec list to the proper position.
The impact here is that there are 10 fields that are off by one and
therefore their spec descriptor is incorrect.

Ideas?

Thanks!

Don

 

[Ken Snell [MVP]]

What version of ACCESS are you using?

 

[Don]

Ken,

I'm using MS Access 2003 (SP1) on an XP machine (SP2).

Don

 

[Ken Snell [MVP]]

You may need the post-SP1 hotfix. Description of the Access 2003 hotfix
package:
http://support.microsoft.com/default.aspx?scid=kb;en-us;837003

The new SP2 for Office 2003 likely contains this fix, but I've seen some
interesting "bugs" reported for SP2, so ......

 

[Don]

Ken,

I will check into it. The big problem might be getting the Help Desk folks
to install it. You know how those folks can be about corporate consistency!

Curiosity questions: Where does Access store the specification data? I
thought maybe a hidden or system table, but I could not find it. Figured if
it would not allow me to edit the import spec during import, I would just
edit the spec directly.

Thanks!

Don

 

[Ken Snell [MVP]]

It is stored in hidden/system tables. From an earlier post by John Nurick,
ACCESS MVP:

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.

--

Ken Snell
<MS ACCESS MVP>

 

[Don]

Ken,

I had looked at MSy­sIMEXSpecs, but because it did not seem to contain the
filed names I didn't explore further. Now looking at MSysIMEXColumns have a
better picture. Was so focused on "specs" the IMEX ("import/export") didn't
click! Live and learn!

Thanks for all the help!!!

Don

 

[Joe Fallon]

FYI - those *tables* are not editable directly.
Try writing a simple Select query using the same fields as the table. Then
run it.
It should ike identical to the table. The *query* is editable.
--
Joe Fallon

 

[Larry Schuller]

Thank you. That appears to have answered both my questions (how to add a
field and how to insert a field in the middle of an import)

Now, why did Microsoft do it that way? (rhetorical)

Larry Schuller