Newbie using ADO vba Excel to connect to a csv text file. I keep getting a type mismatch on the third field. Somehow ADO considers it to be a number field inspite of the fact that several of the column entries are Number/Alpha combos (eg 3a 2b etc). Any suggestions on how I can I avoid the "Type Mismatch" Error? Is there a way to assign types to the fields before the insert?
My code basically uses the sample code from page 401 of WROX Programmers Ref Excel 2002 ... to perform an ADO INSERT on a csv text file.
The 3rd field (batid) should be considered a text field but the ‘aa1’ INSERT keeps generating a ‘type mismatch” error. It will only allow pure numbers.

Here’s the code
'=======================
   szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & stDB & ";" & _
                "Extended Properties=Text;"

    Set objCommand = New ADODB.Command
    objCommand.ActiveConnection = szConnect

    objCommand.CommandText = "INSERT INTO " & FileName & "(Insid, insdate,batid) " & _
    "VALUES('TestINS','1/1/2001','aa1');"

'=======================
Rene


Rene

u can use a schema.ini file to tell the text driver what all the fields are. see here for details:
http://msdn.microsoft.com/library/de...a_ini_file.asp

That WORKS!
Wow...Thanks, and so quick too!.
Was trying to find a sample of a schema.ini and found an easy way to make em by using the Administrative tools/Data Sources (ODBC)in the Control Panel.
First "Text Files" then "Configure".
Uncheck "Use Current Directory".
Click "Select Directory" to locate directory the source files are. Click "Options" "Define Format". In the "Define Text Format" dialog, Select each csv source file,
  Click Column Name Header (in my case)
   then Guess.
Finally click OK
then CANCEL.
A schema.ini file gets created in the source file directory.
I had to edit it to add "Char" after each char field. For some reason the process I just described, defines all the other fields but not the Char and this creates an error "The Col 1 option is invalid".
Thanks again.
Rene


Rene

Hey, nice tip about creating the schema.ini. Thanks.

Phil