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

ID: Q223198

The information in this article applies to:

Microsoft Access 2000

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

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

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)

CAUSE

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

RESOLUTION

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

STATUS

This problem no longer occurs with Microsoft SQL Server 7.0.

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

Create a new DSN called TestTimeStamp that points to the Pubs database on your SQL Server 6.5 computer.

Open a new, blank database.

Create a new query.

Click Close in the Show Table dialog box.

On the Query menu, point to SQL specific, and then click Pass-Through (this establishes the new query as a pass-through query).

On the View menu, click Properties to open the Query Properties window.

In the ODBCConnectStr property, enter the following:


   ODBC;DSN=TestTimeStamp;UID=sa;PWD=;Database=Pubs

Type the following into the SQL Pass-Through Query window:


   Create table t_TimeStamp (col1 timestamp)

On the Query menu, click Run.

Click OK when you get a message similar to:


    Pass-through query with ReturnRecords property set to True did not
    return any records.

Replace the SQL statement that is in the SQL Pass-Through Query window with the following:


    Insert into t_TimeStamp values (NULL)

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)

Additional query words: pra

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug