Entering Date via SQL Query

A

ajitrajput

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

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

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

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

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

Please help me !!! Trying from last 2 days.
 
B

Bob Phillips

Have you tried using a string in the SQL Insert statement?

--

HTH

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

Jamie Collins

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')
Click to expand...

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/06/external_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.

--
 
A

ajitrajput

Jamie said:
*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 a 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 t the
respective column as (mm/dd/yyyy), but still it doesn't work. want to
programmatically enter the date value without any single-quote i. the
value should be treated as date datatype.

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

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

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

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

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

Once Jet has determined that the majority type for the column is
'Date' (adDBTimeStamp) then it should be able to coerce a
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 us
the
Microsoft OLE DB Provider for Jet and the insert works fine wit
this.

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

Jamie.

-- *
Click to expand...


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

Thanks in advance.....
 
B

Bob Phillips

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 > said:
Jamie said:
*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')
Click to expand...

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/06/external_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.

-- *
Click to expand...


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......
Click to expand...
 
Top