Import Wizard dropping fields defined by Import Spec Access 2007

[DinosRose]

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? Is there a fix? 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?

Thanks in advance....

 

[John Nurick]

Comments inline:

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)?

 

[DinosRose]

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.

 

[John Nurick]

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.

One way round that is to add fields to the table to store information
about the source (e.g. filename and date imported) and status of the
data. (If you have multiple tables with the same structure but from
different sources or dates you are effectively storing data in the
table names, which is a no-no as far as RDB theory goes.)

But if you want to continue with multiple tables, you can easily
create a new table before each import. Assuming table TTT has the
structure you need and the string strTableName contains the name for
the new table, use something like this:

...
Dim strSQL As String
...
strSQL = "SELECT * INTO " & strTableName _
& " FROM TTT WHERE FALSE"
CurrentDB.Execute strSQL

Then import the file without an import spec.

 

[DinosRose]

Given that the database users need to move data in and out of their tools to
not exceed th 2GB size limit and the fact that this is an existing tool with
extensive VB designed around the table name, I think it is more practical to
use separate tables rather than adding fields to store information about the
source. It's too late in the game to change the entire process to be able to
manage subsets of data within a single table through the flow of the entire
application (company is upgrading everyone by EOY).

I think I'll be able to use a cross between what you've suggested for option
2 with a hybrid of some code I've used for some other situations
(docmd.transfertext, where path is pre-defined and name of file is selected
from a List Box of .csv files saved at that path, combined with the process I
used to append data to a table I make ahead of time when pulling data from
Teradata using an ODBC connection, CurrentDB.Execute strsql where strsql is a
CREATE TABLE query).

It'll just take some time to re-do the forms the users utilize to import the
data into their tools.

Thank you for your help.