How to change ACE TypeGuessRows Registry Value for Access 2007 to Determine Field Data Type Properly When Importing From Excel File Using VBA -

i have access database imports data external excel spreadsheet contains column of data called date delivered. while data in column date type, entries 4/5/15,5/1/15 made indicate 2 delivery dates.

this trips access when importing data using:

docmd.transferspreadsheet acimport, 5, "delivery data", "\\file\path.xlsx", true  

this causes make column date type rather string drops value fields problematic. found appeared helpful solution here suggested changing typeguessrows registry value 0 make jet @ rows when determining field data type.

i created , changed registry value using following code:

dim regloc string dim myws object  'writes registry value make jet check rows (0) not first 25 (default value = 8) determine type regloc = "hkey_current_user\software\microsoft\jet\4.0\engines\excel\typeguessrows" set myws = createobject("wscript.shell") myws.regwrite regloc, 0, "reg_dword" 

unfortunately did not seem work problem persisted after changing value , restarting access , excel.

is there separate registry value access vs excel jet engine or not think should?

edit:

looks excel 2007 , newer (i using 2007), registry value should be:

hkey_current_user\software\microsoft\office\12.0\access connectivity engine\engines\excel\typeguessrows 

however still isn't working.

edit 2:

here code @practlogical suggested:

sql = "create table [delivery data import] ([f1] text, [f2] text,[f3] text, ...)" currentdb.execute sql  docmd.transferspreadsheet acimport, 5, "delivery data import", "\\file\path.xlsx", false  sql = "insert [delivery data] ( buyer, [po #], [receipt status],...) select [delivery data import].f1, [delivery data import].f2, [delivery data import].f3,...] (get rid of headers);" currentdb.execute sql 

i still looking 1 , done solution imports via registry key if exists (knowing doesn't exist useful).

i'm unable re-create issue, have run in past. no clue why dropped import specifications perfect such things, i've had luck doing following (not typeguessrows related).

make copy of [delivery data] table , name [import_deliverydata]. delete data in copy, go design view , change cols short text data type. bit of hassle, you'll need rename cols f1, f2, f3, etc starting top col f1. *you'll see why in sec.

now, transfer sheet import table (deleting existing rows first) , sure *hasfieldnames false. should come in fine text. you'll need cleanup on import table's data before appending 'real' [delivery data] table, @ least have control on what's what.

from experience, access checks first row of data determine data type column is; second row if hasfieldnames true. here telling check first row should text because it's field names. not that, we're telling append existing table of text columns instead of (re)creating table. helps when access thinks column date, creates date column it, , tries append non-dates...which cause of issue.

i use type of import proxy table when importing excel since data can unpredictable.