transfertext imports incorrectly - weird values

[james.elks]

i've been trying to automate a text import. When i use "Get External
Data" the process works fine, but does not work when I try to use a
macro or VB code.

here is the code i tried (and yes, i tried using the macrto function
also):

Private Sub cmdImp_Click()
Dim dbPath As String
dbPath = Application.CurrentProject.Path
Dim accPath As String
accPath = dbPath & "\acc.txt"
DoCmd.TransferText , , "acc", accPath, -1
End Sub

The txt file is comma separated and has field names. I am importing
into an existing table after having deleted all existing records. The
data goes into the table ("acc"), but the first column has incremental
values in it (as if it were Autonumber) instead of the values in the
txt file. The numbers in the first column often start with a zero so
data type is String...

any ideas?

thanks!

[pietlinden]

did you create an import spec? I don't see you passing the import
specification in the import routine. If you do that, you force the
fields to be interpreted in the way that _you_ chose, instead of
leaving Access to figure it out for you

[james]

Yes, I also tried that (going into External Data, Advanced...) but that
didn't seem to help. I shouldn't need an import spec anyway since the
column headings are the same in the text file as in the database (and
plus, it csv - doesn't get much simpler than that).

Any other ideas?

[pietlinden]

Not sure this will help at all, but could you post a few records so I
can try to sort out the import? Oh, and include the field types, so I
don't think I have a solution when I really don't.

[james]

Sounds damn good, thanks! The data is sensitive to some degree so I'll
change the values slightly and send it on.

thanks!

[pietlinden]

james wrote:

Sounds damn good, thanks! The data is sensitive to some degree so I'll
change the values slightly and send it on.

thanks!

I'm just interested in data size and type, so munge away. Basically,
keep it accurate enough so folks here can reproduce the problem. (data
types/patterns are the same).

[james]

got it...

When creating your Import Spec, go all the way to the end of the "Get
External Data" screen before clicking advanced, then saving the
settings...

thanks.

[pietlinden]

Yeah, it's one of those features... you have to go all the way through
the wizard before it saves everything. But then everything after that
works a champ.