ACC2000: Error Message When Inserting Null Value into a TimeStamp Field of a SQL Server Table

View products that this article applies to.

This article was previously published under Q223198

This article applies only to a Microsoft Access database (.mdb).

Advanced: Requires expert coding, interoperability, and multiuser skills.

↑ Back to the top

Symptoms

When you try to insert a Null into a TimeStamp field on SQL Server, you may receive the following error message:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]The user can't INSERT a non-null value into a TIMESTAMP column. Use INSERT with a column list or with a default of NULL for the TIMESTAMP column. (#273)

↑ Back to the top

Cause

The Use ANSI nulls check box is selected (checked).

↑ Back to the top

Resolution

When creating the Data Source Name (DSN), make sure the Use ANSI nulls check box is not selected (is unchecked).

↑ Back to the top

Status

This problem no longer occurs with Microsoft SQL Server 7.0.

↑ Back to the top

More information

This error can occur when you use Microsoft Access 2000 to pass queries to Microsoft SQL Server 6.5.

Steps to Reproduce Problem

1.	Create a new DSN called TestTimeStamp that points to the Pubs database on your SQL Server 6.5 computer.
2.	Open a new, blank database.
3.	Create a new query.
4.	Click Close in the Show Table dialog box.
5.	On the Query menu, point to SQL specific, and then click Pass-Through (this establishes the new query as a pass-through query).
6.	On the View menu, click Properties to open the Query Properties window.
7.	In the ODBCConnectStr property, enter the following. Note In the following sample code, you must change UID=`<username>` and PWD=`<strong password>` to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database. ODBC;DSN=TestTimeStamp;UID=<username>;PWD=<strong password>;Database=Pubs
8.	Type the following into the SQL Pass-Through Query window: Create table t_TimeStamp (col1 timestamp)
9.	On the Query menu, click Run.
10.	Click OK when you get a message similar to: Pass-through query with ReturnRecords property set to True did not return any records.
11.	Replace the SQL statement that is in the SQL Pass-Through Query window with the following: Insert into t_TimeStamp values (NULL)
12.	On the Query menu, click Run, and then click OK when you get a message similar to the following: ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]The user can't INSERT a non-null value into a TIMESTAMP column. Use INSERT with a column list or with a default of NULL for the TIMESTAMP column. (#273)