MS Access VBA / SQL Import Error

Question

I am writing an SQL in VBA for a  text (.CSV) import into MS Access, currently i am testing it on one field which contains text and numbers, however the after running the following statement the numbers come in and the text doesnt't.

CSV File INfo -

field IMA contains data such as e.g. 8959 and 3SD87 - These are reference numbers (duplicates etc)

The CSV is comma delimited and has no text qualifiers.

HEre is the VBA code i am using

Dim str As String

str = "INSERT INTO [TEMP] ([IMA]) " & _

         "SELECT cstr([IMA]) FROM " & _

         "[Text;FMT=Delimited(,);HDR=YES;CharacterSet=437;DATABASE=C:\Documents and Settings\Desktop\Processed Files].[D1.csv]"

Debug.Print str

DoCmd.RunSQL str

however if i used the text import wizard and declare the field as text it comes in fine.

any ideas on how i can alter this statement to have the same result.

Answer 1

Same issue occurs with transfertext, all of the "3SD57" References are excluded from the Import.

The table i am import them into has no field restriction and the field is already descripbed as Text

All other fields work except for this one.

Answer 2

You can still use a TransferText and prompt for the filename first.  You are going to have to do that anyway.

Answer 3

No its not always going to have the same name or same location and table as i want to keep it dynamic.

Answer 4

Is the file always going to have the same name and location? If so, I would just link to it or Do a TransferText, then Append.