excel - Auto importing into access - forcing field type -

i have table in microsoft access database import data every day daily performance reports. particular field in imported data (referring site name), text field in source data reports mix between text fields (e.g. site 00415) , numerical fields (e.g. 00415) 1 report. when data being stored in excel forced numerical sites text since of them had leading zeros.

the access table has data type text (and format @ should relevant) when importing reports numerical sites import errors on lines have text entries since has tried import whole column numbers rather text.

is there way force field imported text when first line of file (which believe access bases field type on) numerical?

alternatively, should change field type else can handle text , numbers importantly leaves number without formatting in way.

for worth - have been importing data via saved import method , via scripting following script , experienced problem in both import methods:

option compare database  sub import_test()   dim strxls string   strxls = "c:\users\me\desktop\import.xlsx"    docmd.transferspreadsheet acimport, , "importdb", _     strxls, true, "a1:z100"  end sub 

by default, access check first 24 rows of data in order determine suggested types fields.

if data numeric first 24 rows, access may have guessed @ incorrect data type field. data-type saved along saved import, , access makes hard view field type saved.

it sounds table set short text , @ format. ideal needs.

you'll need make sure excel file has text formats values - particularly values leading zeros. you'll need run import excel file wizard again, time sure explicitly set field types each column, taking particular note of problematic field - may find access assumes field should double, when should short text.

once you've done that, you'll have use docmd.runsavedimportexport approach, because transferspreadsheet approach doesn't let specify field type detail excel files.

docmd.runsavedimportexport "myimport" 

and here's text-formatted values in text field.

should have ongoing issues, might have export excel file text file format, , use access import file, doubt you'll need that.