How to do, via vba, to create the MSYSImexSpecs and MSYSImexColumns tables? (1 Viewer)

[ebs17]

I mean both variants.
The cases where the importing table and database table are identical in structure (same number of fields and identical field names) and are simply appended are rare in my practice.

A real import looks like that only some fields are taken into account during the import, whereby fields have different names. It will often be the case that only new records have to be imported - a double import can sometimes occur, but no one needs duplicates, neither does index errors.
Therefore, for me, a linked table is already a table that can be used in queries (update and append) and that can make a lot of modifications in the queries.
An import can also quickly appear in such a way that the data in the import table has to be divided into several tables in a database schema, including the generation of the necessary keys for the relationships. That means using queries again. All this also works with a linked table.

A previously imported table that does not fit into the existing database schema is only a bad intermediate step, because it is a temporary intermediate step and then waste data that has to be disposed of. I would only consider such a preliminary import to be useful if fields in this table are indexed specifically for performance - or out of necessity because a linked table does not work or cannot be created.
Linking a table during an import is therefore my first choice and quite important.

 

[Pat Hartman]

I think you didn't understand what I said. Linking to the import file and then using an append query to do the actual transfer, allows you the opportunity to convert code fields if necessary, choose only specific columns to import, choose only specific rows to import as well as validating data and backing out the append if there are errors. I'm not sure what type of applications you build but if I import data from external applications, the format is fixed. The link and append with validation are only necessary because you simply cannot trust files that may be touched by users. It is what we call in the bridge world, a safety play. You protect yourself from bad data as best you can.

I don't know why you keep thinking that I am importing the data into temporary tables first. There are situations where I might do that if the validation is too complicated for a query but in that case, I would NEVER import into a permanent FE or BE, I would use a temp BE that can be discarded once the import is complete.

And finally, to minimize the potential for the user trying to import the same file multiple times, I keep a log that includes the source file and path names and when the import happened. For bank statements which MUST be imported sequentially, I take that into consideration also. All appended rows include the ID of the log record so if a mistake got by me, I can easily remove all rows that were imported that should not have been.

None of this is made possible by using the "new" method of saving an import. And that Is why I'm sticking with what actually works until MS removes the feature. At that time, I hope to not care. If I still care, I will just use FSO and roll my own.

Just because something is "new" doesn't make it better. I'm pretty sure that some bright bulb on the Access team decided to "simplify" the import/export process rather than creating meaningful documentation or providing a direct way to modify an existing spec. It is simply not obvious that to modify an existing spec, you just try to do the import/append/link again and press the advanced button to call up the old version. People don't discover that by accident just as they will never discover a way to use the "new" spec the way that the "old" spec can be used.