Import error for large negative numbers from CSV to Access using TransferText -

i'm importing data csv file access table. number like
-21000000 (-2.1e7). i'm using transfertext import.

docmd.transfertext acimportdelim, , "matching report temp", source_folder & "\" & source 

in "matching report temp" table, field set double. import generates type conversion failure. however, when open csv file in excel , copy offending row, can use paste append add table manually - number doesn't exceed capacity of field.

as far can tell, happens large negative numbers. smaller numbers, , positive numbers of same magnitude seem import fine. know can specify import specification in transfertext, don't see way set field type double.

how can around problem? don't want have manually track down import errors , append them hand.

don't let transfertext create access table you. create access destination table first , assign field types need.

when run transfertext, csv data appended existing table. should work without error long choose compatible data types access fields.

i examined csv file. header row troublesome because field names missing:

reporting unit,,$ dollars,offset unit,,$ dollars,variance 

that seemed complication docmd.transfertext. imported manually access ui. , gave me import error on row 49 3rd , 6th columns. in access, destination fields both created long integer. values fields in csv row 2262169190 , -2262169190 ... both beyond capacity of access' long integer, -2,147,483,648 2,147,483,647.

as cheap workaround, selected text data type of 2 columns when imported. allowed import work without error. once data access successfully, cast (string) values numeric type when need use them.

it worked when chose double type 2 columns. sounds that's want.

if want import working docmd.transfertext, think you'll need create import specification. if possible, first modify header line of csv file fields have names.

i tested approach without altering csv file. after creating import specification in access ui (see screen capture below), transfertext operation worked in access 2007 without import errors.

docmd.transfertext acimportdelim, _     "ic_y1301_specification", _     "ic_y1301_ld10279_f25210001", _     "c:\users\hans\downloads\ic_y1301_ld10279_f25210001.csv", _     true