Query w/Date Criteria May Cause An ODBC Error

SUBJECT Query w/Date Criteria May Cause An ODBC Error

The information in this article applies to:

• Microsoft Access 97
• Microsoft Jet 3.5

SYMPTOMS When you run a query based on a linked (attached) Microsoft SQL Server table that contains a Datetime field (column) and you specify criteria for this field, Microsoft Access will give the following error:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

when using Microsoft ODBC driver version 2.65.0252, or the following error:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification

when using driver version 3.50.0305 or 3.60.0319.

When you run a query based on a linked (attached) Oracle Workgroup Server 7.3 table that contains a Date field (column) and you specify criteria for this field, Microsoft Access will give the following error:

[ODBC] Invalid literal string

when using Oracle ODBC driver version 2.5.3.1.0b.

CAUSE A bug in the way Microsoft Jet 3.5 sends date/time criteria to the ODBC driver. When a linked SQL Server table contains a field with a data type of Datetime and you specify date criteria like #10/10/1999# for it into a query, Microsoft Jet sends the following command to the SQL Server ODBC driver:

SQLExecDirect: SELECT dbo.tablename.prkeycolname FROM dbo.tablename WHERE (datecolname = {ts '1999-10-10 00.00.00'} )

The "ts" notation between curled braces is an ODBC escape clause in which "ts" stands for "timestamp". The same thing happens with the Oracle ODBC driver. One problem is that dot used as separator in the time portion of the value is a syntax error (colon is needed). Another problem should be the entire date/time value used for a date only criterium.

Changing the time separator from "." to ":" (colon) in the international settings doesn't affect Jet's behaviour.

RESOLUTION Install Jet 3.51 (FTP address is:
ftp.microsoft.com/softlib/mslfiles/jet35upd.exe). Jet 3.51 sends a different command to the ODBC driver, i.e., it uses the "d" escape clause instead of the "ts" for date-only criteria. So the query above will be passed as:

SQLExecDirect: SELECT dbo.tablename.prkeycolname FROM dbo.tablename WHERE (datecolname = {d '1999-10-10'} )

What's more, if criteria include a user specified time portion, this is passed in the form of a "ts" escape clause with (correct) colon separators.

MORE INFORMATION This problem seems unknown to Microsoft.

KEYWORDS Microsoft Access 97 Access97
Microsoft Jet 3.5 bug
ODBC driver
Date Datetime Timestamp
ODBC escape clause clauses
{ts {d
SQL Server 6.5 Oracle Workgroup Server 7.3.3 Italy Italian international settings

#####

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 28 1999 - 12:39:57 CST