By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,126 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,126 IT Pros & Developers. It's quick & easy.

TransferText doesn't work like Menu Import

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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)

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.