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

Article translations Article translations
Article ID: 252699 - View products that this article applies to.
This article was previously published under Q252699
Expand all | Collapse all

On This Page

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.

Properties

Article ID: 252699 - Last Review: January 5, 2007 - Revision: 5.1
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
Keywords: 
kbbug kbdatabase kbjet kbpending KB252699

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com