What does yours look like? Anything like this?

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mySpreadsheet.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ajitrajput " wrote in message
...
Jamie Collins wrote:
*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.

-- *


Can you please pass on the code/connection-string where you have used
OLE DB Driver. It's giving an error to me. :(

Thanks in advance......


---
Message posted from http://www.ExcelForum.com/