Import from outside db like Excel??? Conversion error!

  • Thread starter Thread starter sbcglobal
  • Start date Start date
S

sbcglobal

I have code like this to import from excel into access:

SELECT INDEX, ...DM
FROM [Excel 8.0; Extended
Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invoice.xls].[BASE$]

problem is: field DM (as diameter) has both number and text (like N/A when
it doesn't exist)..under windows registry, the setup allow Access to look up
1st 20 cells of the col and then it decide the column is 'number'...which I
don't want. I want to import it as text.

I like some expert opinion on how to maneuver this in vba or sql query...NOT
from excel side (like to insert rows or reset excel sheet value..)

I have tried: str(DM) as DM, but it wont' work since function str only work
with well-formated values. And formatting DM column as text won't work
either. Access take it as number (double) anyway. I also tried more
complicated way, like IIF(Is numeric(....)), won't work either.

Generally speaking, is it possible to control Access to take value as Text
(or any other format), independend of what the 'True' format of data
resource????

Many thanks@@!!
 
If you use the wizard to do an import for the first time, you can click on
the advanced tab and specify that this specific field is text. You can then
save the import script. If you then do further imports programmatically, you
can reference the name of this script so that all future imports will bring
the field in as text.
 
Back
Top