Import specification "wizard" tips sought

[TheAntiGates]

Any clever intermediate/advanced tips for this?

Pre-A2000 I often created long, excruciatingly prepared specs, and it seemed to often lose records - it SEEMED that if I had over 25 fields this happened. Anyone know if this was addressed, or tips or issues in the interest of suicide prevention after building and losing a 94 field specification?
- It's in a system table. It's actually possible to get a system table into datasheet view. But you can't change it (well, maybe you can corrupt it).
- In the dialog (titled <Mytable> Import Specification) you can't insert rows - can you? Amazingly, I divined how to delete rows (alt-space or click/mark the row, and hit delete key) - but in my experience that either fails or corrupts the table.

Or CAN any of these be done? Especially inserting. What do you do when I.T. adds the 95th field in the middle of the layout?

Got any obscure tips for taking productive advantage of saved import specifications?

A2003.

[Norie]

You can actually import the specification table from 1 database to the other.

Don't know if that helps at all? :)

[TheAntiGates]

Thanks to all who thought about this. :beerchug:
I see two reasonable approaches from 2 known access experts - one involving manipulating the hidden table, and the other using clever cut-and-pasting. They're untested here, so YMMV; moreover they might not apply to your version. But this is the only solution I've ever seen to the insert problem.

http://groups.google.com/group/microsoft.public.access.externaldata/browse_frm/thread/1af09f9ff53e3e21

[TheAntiGates]

The paste method practically seems to have to be done one row at a time - too much work (often). Otherwise you can try selecting the last row and press the insert key (for a bunch of rows to paste at once), and it gives an error message and doesn't show the paste. Microsoft: we make software that enhances and inspires you.

As to the query approach:

First add the new row with their standard lame interface (to the end). Then
SELECT Attributes, DataType, FieldName, IndexType, SkipColumn,
SpecID, Start, Width
FROM MSysIMEXColumns
ORDER BY Start;

Run the query as shown above, which I stole from the referenced post I mentioned above, but which additionally sorts ascending on field Start. Run it to get a feel for what's up, but more importantly, observe the Start position of where you want to insert.

Let's say you want to insert a new Field 24, and the old field 24 should become field25, etc. Let's say that Field 25 used to start in column 83 for a fixed width record layout, and now it should be column 88.

Copy the select query to an update query, merely changing Start to
IIf([Start]<83,[Start],[Start]+5)

(Note: if the input text file is a .CSV file or otherwise delimited, this still needs to be done because the Start field controls sequencing. Ignore that the Start values/widths are essentially jibberish; but respect the control that they wield!)

Run the select query again to see the last record. (See the gap in Start/Width where you carved out a 5 byte hole?) Let say its Start is 197 -- and we already know its Width is 5. So create one more Update Query,almost identical to the other one, with Start becoming
iif (Start<>197, Start, 83)

At least in my testing, that gets her done. But what a load of Spit that this is all required. MS: lame! Pathetic! Besides, I've leaned on conjectures to even get to this point.So YMMV! Work from a copy!! Heil Gates!

[geekgirlau]

Another approach that I have used on occasion is to link directly to text file and use an append query to transfer the records to the Access table - gives you a bit more control over the data structure without having to mess with import specifications.

[chocobochick]

What geekgirlau said. I avoid the Import Wizard at all possible costs, because I've had too many things go wrong.

Instead, I use linked text files to facilitate most of my regular updating. First, I create a dummy text file at a dedicated path. Then I link it to Access as a new "table." Dealing as best I can with the Link Wizard, I specify whether or not I intend to use the first row as headers (which varies, depending upon the reporting systems I'm importing) and make any needed clarifications on data types.

Then I make an append query that references the text file's table, set to append new information into a genuine Access table (which is also linked from another database file -- I keep all my tables in one database and link them to my "application database" so my forms, queries, and reports can work with the data but doesn't allow a user to accidentally delete or modify the table's design). In the query, I make sure to set any needed criteria to avoid conflicts with primary key fields, overwritten values, etc.

It's a complicated setup, but once this part is done, the rest is easy. Each week, I run a few system reports that spit out delimited text files. I copy each file to overwrite the corresponding dummy file (or previous week's report). Then I hit a button on the Access form that runs the append query for me, updating my data.

[TheAntiGates]

I roll my own text file imports also in many cases, but...

The critical feature of the [albeit of flawed design] import specifications interface is the skipping of some fields - e.g. when only fields 1-9, 21, and 89 matter to you. This gets critically important when you start to deal with big, big files. If you were to pull every field of every record, and the wind is blowing just right, you just MIGHT be a - going over 2 Gig filesize, which is about as pleasant as being at a CarrotTop concert.

Still you've inspired me to revisit my VBA text file import routines to see if I can pare the unneeded fields. However, one problem that I've had with this (using Recordset .addnew and .update) is that the .MDB swells by perhaps 2 to 4 times the size of the incremental (imported) table -- ergo the 2 Gig gorilla again beareth down. (It's particularly vexing when it happens on one huge table, thereby preventing you from saving yourself by compacting between steps. Even THEN, having to repeatedly compact, just to prevent the 2 Gig nausea, is a painful way to live.)

At least by using the import specification (rather, using MSysIMEXColumns), the slop factor is low, so I put up with it as such.