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

This article has been archived. It is offered "as is" and will no longer be updated.
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.
More information

Steps to Reproduce the Behavior

  1. Create a new Microsoft Access 97 or Microsoft 2000 database.
  2. Create a new Microsoft Visual C++ Win32 Console application.
  3. Copy the following code into the application:
    #include <windows.h>#include <sql.h>#include <sqlext.h>#include <tchar.h>#include <stdlib.h>#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");}						
  4. Change the connection string to reflect the location of your Access database.
  5. Compile and run the application. Observe errors.
datetime incorrect insert over flow 1753 1754 1800
Properties

Article ID: 252699 - Last Review: 01/10/2015 12:23:55 - Revision: 6.0

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • kbnosurvey kbarchive kbbug kbdatabase kbjet kbpending KB252699
Feedback