TransferText doesn't work like Menu Import

[Typehigh]

I am a pretty saavy user, but I am having a real problem with the
DoCmd.TransferText operation.
I manually used the File>Get External Data> Import routine to set up an
import specification. It works like a champ everytime it's run.

If I use VBA to execute a TransferText and specify the specfile, I get
a Type Conversion Error on import and a table full of empty records
(equal to the number of lines in my import data file).

My data source file is a fixed-length text file.

Here is my VBA line:

DoCmd.TransferText acImportDelim, "MySpec", "tblTemp", "C:\MyData",
False

I have tried a lot to get this to work.

I first let Access import it into a new table that it created. Then I
opened it up and copied down the data types. I then specified the same
data types in my import table (tblTemp).

The data file has 10 fields. The first and seventh fields are vbLong
types, the others are vbText types.

It's always erroring out on the first field.

Again, it always works fine when manually launched through the menus.

Any suggestions? I'm stuck until I find a solution. Thanks in advance.

[David Lloyd]

One observation is that you say the file is fixed length yet you are using
the acImportDelim parameter rather than the acImportFixed.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Typehigh" <nm******@usatoday.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
I am a pretty saavy user, but I am having a real problem with the
DoCmd.TransferText operation.
I manually used the File>Get External Data> Import routine to set up an
import specification. It works like a champ everytime it's run.

If I use VBA to execute a TransferText and specify the specfile, I get
a Type Conversion Error on import and a table full of empty records
(equal to the number of lines in my import data file).

My data source file is a fixed-length text file.

Here is my VBA line:

DoCmd.TransferText acImportDelim, "MySpec", "tblTemp", "C:\MyData",
False

I have tried a lot to get this to work.

I first let Access import it into a new table that it created. Then I
opened it up and copied down the data types. I then specified the same
data types in my import table (tblTemp).

The data file has 10 fields. The first and seventh fields are vbLong
types, the others are vbText types.

It's always erroring out on the first field.

Again, it always works fine when manually launched through the menus.

Any suggestions? I'm stuck until I find a solution. Thanks in advance.

[dkintheuk]

The type conversion error indicates that the fields are not exactly as
you think they are... normally.

The fact that the import works manually with the MySpec specification
should mean that it should work with the acImportDelim...

However, you might have come across the same problem that i did, in
that the end-of-line characters can appear to be fine but when you try
to do anything in VB, you just get errors.

You might find that there are spurious characters in the input file.

Try a hex file reader and walk through the characters in the file to be
sure there is nothing wierd in there then post back what you find and
we'll all go from there.

Rob.

[Typehigh]

David your observation was right on the money. I changed the Transfer
Type from acImportDelim to acImportFixed and it worked perfectly.
Thanks for such a quick reply, I would have never discovered my
oversight.

Thanks again,

Neil (Typehigh)