Re: Import Wizard dropping fields defined by Import Spec Access 2007

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for the feedback. Unfortunately, I don't believe importing into an
existing table is an option in this case for at least a couple of reasons.
1) The auditors (employees) have several different sets of data in their
tools at any one time (i.e. data is imported for today's report but review is
not complete for a couple weeks, but more data is imported on a daily basis).
We can't have the new data replacing data that is still being reviewed from
an existing table. And 2) for purposes of downstream reporting, the table
needs to maintain the name of the file being imported and cannot have a
generic name of an existing table.
--
-DinosRose - sometimes I think I have gotten in over my head...


"John Nurick" wrote:

Comments inline:

On Tue, 4 Dec 2007 11:45:05 -0800, DinosRose
<DinosRose@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I support several database "tools" in my department at work. Most of them
use an import specification to get data either through visual basic or
manually. The import specs in a couple of these tools have some "tack on"
fields included after those fields that are included in the "standard" .csv
being imported to support additional fields that are contained when the
"optional" .csv needs to be imported (all of the first x columns are the same
but "optional" data has extra fields on the end). The specs are set up this
way so that the forms throughout the tool can support both the standard and
optional datasets by having "filler" fields.

I have never encountered a problem with this from Access 97 through Access
2003. In the process of testing the tools (still in .mdb format) using
Access 2007, I discovered that the "tack on" fields are being dropped at some
point during the import wizard if they don't actually have data in them.
They show up when I select my import spec file, but are gone by the time the
wizard has completed.

Any ideas what is causing this?

Only that there's been a tendency for successive versions of Access to
be stricter about the text file data they will accept, and this could
be the Access 2007 instance.

Is there a fix?

Not if my suggestion above is right.

Or am I going to need to
develop a work around process where the user has to indicate whether they are
doing a "standard" import or an "optional" import so it can use different
import specs and then run a setup on the "standard" import to append the
filler fields using the tabledef property?

You seem to be importing each text file into a new table with specific
field types: otherwise you wouldn't normally need to use import specs
with CSV data. Have you considered importing into an existing table
(assuming the CSV files have header rows with the field names)?
--
John Nurick - Access MVP

.

Relevant Pages

  • Re: Your 10 y-o will *WANT* it
    ... Mate of mine is importing them from China for about a grand, ... built to his specs. ...
    (uk.rec.motorcycles)
  • Re: sending previous "contacts" to new "contacts" folder
    ... Are you "exporting to a CSV" (which is a fairly "standard" method) and then "importing" into Windows Mail? ...
    (microsoft.public.windows.vista.mail)
  • Re: Simple query with "like" not working >:(
    ... The issue was that the specs I was ... provided for importing the SAP report .txt file into Access was wrong. ... My apologies for dragging you guys through the mud on something as ...
    (comp.databases.ms-access)
  • Re: Import Wizard dropping fields defined by Import Spec Access 2007
    ... The import specs in a couple of these tools have some "tack on" ... fields included after those fields that are included in the "standard" .csv ... You seem to be importing each text file into a new table with specific ... with CSV data. ...
    (microsoft.public.access.externaldata)
  • Re: DoCmd.TransferText
    ... for it to be able to accept importing of .txt files into the db, ... The import spec needs to reside in the database file that is running the ... there's no requirement to store the import specs in ... dialog window. ...
    (microsoft.public.access.formscoding)