I will also post this question to another newsgroup as I am not sure if this
is the right place to ask:
I'm using Access' Docmd from VB to import an Excel 8.0 spreadsheet into a
table. It's importing, however, some data from the spreadsheet is kicking
out into an errors table. The reason being a Type Conversion Error.
I want to import EVERY field for what it is. Not based on what Access thinks
it should be!
I have tried to create a table in my code making all fields dbText then
importing, yet Access insists these values be "kicked out". Specifically,
MOST data is numeric but some data has a J or a Q before. (CPT Codes for
anyone whos curious). Even when I link the spreadsheet, I cant refer to any
nonnumeric data in code or I get a Numeric Overflow error (???).
Heres some of my code:
Set AC = New Access.Application
AC.OpenCurrentDatabase PathStr & "CPT_ICD.MDB"
AC.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97 , "CDM",
'I tried the above with no luck so I created a table, then ran the above
Set xlCDM = db.CreateTableDef("CDM")
.Fields.Append .CreateField("F1", dbText)
.Fields.Append .CreateField("F2", dbText)
.Fields.Append .CreateField("F3", dbText)
.Fields.Append .CreateField("F4", dbText)
'The above didn't work, so I linked the darn thing:
Set dbXl = OpenDatabase(mvarCDMFile, False, False, "Excel 8.0;HDR=NO")
As I've said, all import or link fine. Is there any way to tell the Jet
Engine not to evaluate the datatype?