ajitrajput wrote ...

My underlying database is Excel and I am writing all DB related
operations using ADO in Visual Basic Editor of Excel (VBA). I am using
Excel Driver. The data is stored directly into Excel columns as
mentioned in the insert query.

But, when I enter the date value in a column, it attaches a
single-quote(') before the value. I have applied date-formatting to the
respective column as (mm/dd/yyyy), but still it doesn't work. I want to
programmatically enter the date value without any single-quote i.e the
value should be treated as date datatype.

Because of this single-quote(') to the date value which the date-value
in the cell to be considered as TEXT and hence whenever I want to fetch
records between date-range, the SELECT query does not pick-up proper
records.

[Since the end-user of my application will not open the files and make
manual changes, i want the proper date entry to be made without any
single quote...]

Sample Query:
INSERT INTO [mms_instrDR_dtl$](strInstrSeqNbr, dtDR,
dtCreated,strCreatedBy)
VALUES('CLDEL01040400002',#7/21/2004#,#7/21/2004#,'ajit')

You need to get an appreciation of how Jet determines the data type of
an Excel column i.e. it is not just determined by formatting but also
by existing values in the column and machine-specific settings. This
may help:

http://www.dicks-blog.com/excel/2004...al_data_m.html

Once Jet has determined that the majority type for the column is
'Date' (adDBTimeStamp) then it should be able to coerce an unambiguous
date string e.g.

INSERT INTO
[mms_instrDR_dtl$]
(strInstrSeqNbr, dtDR, dtCreated,strCreatedBy)
VALUES ('CLDEL01040400002', '21 JUL 2004', '21 JUL 2004', 'ajit')
;

Having said all that, I can't seem to get it to work with the ODBC
Excel driver via the OLE DB provider for ODBC; I get an error,
'Operation must use an updatable query'. With ADO, I usually use the
Microsoft OLE DB Provider for Jet and the insert works fine with this.

Out of interest, why you are using the ODBC dirver and not the OLE DB
Provider for Jet?

Jamie.

--