Microsoft KB Archive/252699

= FIX: Access ODBC driver can not insert dates prior to the year 1753 =

Article ID: 252699

Article Last Modified on 1/5/2007

-

APPLIES TO


 * Microsoft Open Database Connectivity Driver for Access 4.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q252699



SYMPTOMS
When you insert a date prior to the year 1753 by using Microsoft Access ODBC Driver, the following error message is displayed:

[22008] [Microsoft][ODBC Microsoft Access Driver]Datetime field overflow (null)



RESOLUTION
To resolve this problem, apply the hotfix that is described in the following Microsoft Knowledge Base article:

264081 FIX: Incorrect pre-1753 date values with Jet ODBC driver



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.



Steps to Reproduce the Behavior
 Create a new Microsoft Access 97 or Microsoft 2000 database. Create a new Microsoft Visual C++ Win32 Console application.  Copy the following code into the application:
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include <stdio.h>

void HandleError(SQLHANDLE hHandle, SQLSMALLINT hType, RETCODE RetCode) {   SQLSMALLINT iRec = 0; SQLINTEGER iError; TCHAR      szMessage[1000]; TCHAR      szState[SQL_SQLSTATE_SIZE];

if (RetCode == SQL_INVALID_HANDLE) {       fprintf(stderr,"Invalid handle!\n"); return; }

while (SQLGetDiagRec(hType, hHandle, ++iRec, (SQLCHAR *)szState, &iError, (SQLCHAR *)szMessage, (SQLSMALLINT)(sizeof(szMessage) / sizeof(TCHAR)), (SQLSMALLINT *)NULL) == SQL_SUCCESS) {       fprintf(stderr,TEXT("[%5.5s] %s (%d)\n"),szState,szMessage,iError); }

}

void main(int argc, char* argv[]) {   SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN nstatus; SQLCHAR szConnect[1024]; SQLSMALLINT cbConnString;

//Not checking the return codes in some cases for clarity. nstatus = SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv); nstatus = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0); nstatus = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc); nstatus = SQLDriverConnect(hdbc,NULL,       (SQLCHAR*) "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\db1.mdb",         SQL_NTS, szConnect, 1024, &cbConnString, SQL_DRIVER_NOPROMPT); if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO) {       HandleError(hdbc,SQL_HANDLE_DBC,nstatus); return; }

nstatus = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);

nstatus = SQLExecDirect(hstmt,(SQLCHAR*) "CREATE TABLE DateTable (dateval datetime)",SQL_NTS); if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO) {       HandleError(hstmt,SQL_HANDLE_STMT,nstatus); return; }

TIMESTAMP_STRUCT dateVal; memset(&dateVal,0,sizeof(TIMESTAMP_STRUCT)); dateVal.year = 1750; dateVal.month = 1; dateVal.day = 1;

nstatus = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,       0,0,(SQLPOINTER*) &dateVal,0,0);

nstatus = SQLExecDirect(hstmt, (SQLCHAR*) "INSERT INTO DateTable VALUES (?)",SQL_NTS); if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO) {       HandleError(hstmt,SQL_HANDLE_STMT,nstatus); }

nstatus = SQLExecDirect(hstmt,(SQLCHAR*) "DROP TABLE DateTable",SQL_NTS); if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO) {       HandleError(hstmt,SQL_HANDLE_STMT,nstatus); }

nstatus = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); nstatus = SQLDisconnect(hdbc); nstatus = SQLFreeHandle(SQL_HANDLE_DBC,hdbc); nstatus = SQLFreeHandle(SQL_HANDLE_ENV,henv);

printf("Done"); }                       </li> Change the connection string to reflect the location of your Access database.</li> Compile and run the application. Observe errors.</li></ol>

Additional query words: datetime incorrect insert over flow 1753 1754 1800

Keywords: kbbug kbdatabase kbjet kbpending KB252699

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.