So lately I've been experimenting with using ADO to read/write data to/from Excel workbooks. The recordset gets written successfully but sometimes columns I intended to store numeric have values written to them as strings (adVarChar). I want to find a method which will ensure that data gets written in the correct format.

I can only think of two ways to properly handle this (I've dismissed the possibilities of MaxScanRows and IMEX):
  1. Force each field to the correct Type before writing
  2. Structure the UPDATE/INSERT SQL strings so that each value is written in the correct format for that field.


Can anyone please help with either of the above?