Board index » delphi » MS Access - Date field

MS Access - Date field

I have a very small application which already work with Interbase; now I
try to swith to MS Access.
Via ODBC there is no problem to retrive data (varchar, date, integer), but
I do not manage to write in the table date fields.

I use a query component and I prepare the SQL insert statement with a
parameter.
I assign the param value with:
ParamByName('newdate').AsDate := StrToDate(EditDate.Text)
I get a EDBEngimeError with message 'General SQL Error. [Microsoft][ODBC
Access 97 driver] Datetime field overflow'
If I connect to the ODBC database via Database Explorer the date is
TIMESTAMP type.
I retrive and get data in short format dd/MM/yyyy which is the setting for
my computer regional settings.
Any suggestion would be very appreciated.
Thanks
Paolo Balducci

 

Re:MS Access - Date field


On 17 May 2000 08:25:45 GMT, "Paolo Balducci"

Quote
<[cisa...@mbox.dinamica.it]> wrote:
>I have a very small application which already work with Interbase; now I
>try to swith to MS Access.

Bad move, imho...

Sorry, this is redundant: you already found that out. :)

Jasper D.
(remove the words around @ to reply)

Re:MS Access - Date field


Quote
Jasper Stil wrote in message ...
>On 17 May 2000 08:25:45 GMT, "Paolo Balducci"
><[cisa...@mbox.dinamica.it]> wrote:

>>I have a very small application which already work with Interbase; now I
>>try to swith to MS Access.

>Bad move, imho...

>Sorry, this is redundant: you already found that out. :)

>Jasper D.
>(remove the words around @ to reply)

That's right, but reply like this does not help much.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Re:MS Access - Date field


You can try with native MSACCESS driver or upgade/downgrade ODBC/Access
drivers. When there are many layers (bde/odbc/jet), there are more
possibilities for mismatches.

For example: one layer converts binary data back to string before sending it
to server (looks nonsense, but I've seen this), so it uses local regional
settings which might not match server's.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Quote
Paolo Balducci <[cisa...@mbox.dinamica.it]> wrote in message

<01bfbfda$b84af880$7900a8c0@freud>...
Quote
>I have a very small application which already work with Interbase; now I
>try to swith to MS Access.
>Via ODBC there is no problem to retrive data (varchar, date, integer), but
>I do not manage to write in the table date fields.

>I use a query component and I prepare the SQL insert statement with a
>parameter.
>I assign the param value with:
>ParamByName('newdate').AsDate := StrToDate(EditDate.Text)
>I get a EDBEngimeError with message 'General SQL Error. [Microsoft][ODBC
>Access 97 driver] Datetime field overflow'

>If I connect to the ODBC database via Database Explorer the date is
>TIMESTAMP type.
>I retrive and get data in short format dd/MM/yyyy which is the setting for
>my computer regional settings.
>Any suggestion would be very appreciated.
>Thanks
>Paolo Balducci

Re:MS Access - Date field


On Wed, 17 May 2000 13:59:28 +0200, "Robert Cerny"

Quote
<robert.qwe.ce...@neosys.xrs.qwe.si> wrote:
>Jasper Stil wrote in message ...
>>On 17 May 2000 08:25:45 GMT, "Paolo Balducci"
>><[cisa...@mbox.dinamica.it]> wrote:

>>>I have a very small application which already work with Interbase; now I
>>>try to swith to MS Access.

>>Bad move, imho...

>>Sorry, this is redundant: you already found that out. :)

>>Jasper D.
>>(remove the words around @ to reply)

>That's right, but reply like this does not help much.

Totally correct, Robert, so I apologize. Where would we be without
correcting each others missteps? <g>

Had one of these moods, I guess. ;)

Jasper D.
(remove the words around @ to reply)

Re:MS Access - Date field


Quote
Robert Cerny wrote:
> Jasper Stil wrote in message ...
> >On 17 May 2000 08:25:45 GMT, "Paolo Balducci"
> ><[cisa...@mbox.dinamica.it]> wrote:

> >>I have a very small application which already work with Interbase; now I
> >>try to swith to MS Access.

> >Bad move, imho...

> >Sorry, this is redundant: you already found that out. :)

> >Jasper D.
> >(remove the words around @ to reply)

> That's right, but reply like this does not help much.

> --
> ----------------------
> Regards
> Robert Cerny
> Remove both qwe when replying
> email: robert.qwe.ce...@neosys.xrs.qwe.si

> No questions via email, unless explicitly invited.

 Try to put # in front and at the end. i.e.  #01/01/2000#

--
Wanli Ma
--
Internet Developer
Legend Microsystems, Inc.
http://www.LegendNet.com
mailto:W...@LegendNet.com

Re:MS Access - Date field


Robert Cerny <robert.qwe.ce...@neosys.xrs.qwe.si> wrote in article
<8fu8h1.38...@neosys.xrs.si>...

Quote
> You can try with native MSACCESS driver or upgade/downgrade ODBC/Access
> drivers. When there are many layers (bde/odbc/jet), there are more
> possibilities for mismatches.

How is it possible to use native MSACCESS drivers?
My ODBC Access driver version is 3.50.360200.
Is it related to Office or to Windows NT? Anyway, I will try to get the
update.

Re:MS Access - Date field


Quote
>  Try to put # in front and at the end. i.e.  #01/01/2000#

Thanks but this did not work.
Paolo

Re:MS Access - Date field


Do you have this problem all the time? Or are date's like 1-1-2000 working
fine?
I had a problem like this. MSAccess wanted me to present a date as
MM/dd/yyyy although my short date format was also dd/MM/yyyy
Lookup the where (component) in the ms-access helpfile.

Re:MS Access - Date field


I finally found a combination which works.
I changed the line that assigns the parameter with:
ParamByName('newdate').AsDateTime := StrToDateTime(EditDate.Text)
and now it works even if the field in Access is defined as Date and not
DateTime.
Very strange!
...it works.
Thanks to all for the help (and humor)!
Paolo Balducci

Paolo Balducci <[cisa...@mbox.dinamica.it]> wrote in article
<01bfbfda$b84af880$7900a8c0@freud>...

Quote
> I have a very small application which already work with Interbase; now I
> try to swith to MS Access.
> Via ODBC there is no problem to retrive data (varchar, date, integer),
but
> I do not manage to write in the table date fields.

> I use a query component and I prepare the SQL insert statement with a
> parameter.
> I assign the param value with:
> ParamByName('newdate').AsDate := StrToDate(EditDate.Text)
> I get a EDBEngimeError with message 'General SQL Error. [Microsoft][ODBC
> Access 97 driver] Datetime field overflow'

Re:MS Access - Date field


BDE can "talk" to Access via ODBC or directly. See drivers page in BDECFG or
BDEADMIN.
When "talking" directly, it "understands" better Access than through generic
ODBC layer.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Quote
Paolo Balducci <[cisa...@mbox.dinamica.it]> wrote in message

<01bfc09e$99b2a250$7900a8c0@freud>...
Quote
>How is it possible to use native MSACCESS drivers?
>My ODBC Access driver version is 3.50.360200.
>Is it related to Office or to Windows NT? Anyway, I will try to get the
>update.

Re:MS Access - Date field


Thank you, Robert.
I did not consider this option.
Now that I could work via ODBC I tryed direct Access driver:
the result is that I must use DateTime field as well (and this is not a
problem) but MS Access driver does not *speak* SQL correctly: I had to
change the character % with * in my queries.
In my opinion this is a drawback that should be balanced with the advantage
of having less layers in between.
Any comment would be appreciated!
Regards,
Paolo

Robert Cerny <robert.qwe.ce...@neosys.xrs.qwe.si> wrote in article
<8g0jep$k...@bornews.borland.com>...

Quote
> BDE can "talk" to Access via ODBC or directly. See drivers page in BDECFG
or
> BDEADMIN.
> When "talking" directly, it "understands" better Access than through
generic
> ODBC layer.

Re:MS Access - Date field


With Direct MSACCESS driver I also experiment some problems when fields are
null: there are problems retriving these fields (Error converting from
VARIANT) so that before retriving I must test if the field is null.
With ODBC this problem does not appear.
This is not to charge you with problems. Just to let you know and
eventually discuss.
Paolo

Robert Cerny <robert.qwe.ce...@neosys.xrs.qwe.si> wrote in article
<8g0jep$k...@bornews.borland.com>...

Quote
> BDE can "talk" to Access via ODBC or directly. See drivers page in BDECFG
or
> BDEADMIN.
> When "talking" directly, it "understands" better Access than through
generic
> ODBC layer.

Other Threads