ACC2000: Error Message: DBC Update ON Linked Table TABLE NAME Failed (305617)

SYMPTOMS

When you use Microsoft Access to update or insert a record into a linked Microsoft SQL Server table, you receive the following error message:

ODBC Update ON Linked Table <TABLE NAME> Failed. You cannot save this record at this time. Microsoft Access may have encountered an error.

CAUSE

This behavior occurs when Microsoft SQL Server does not return the message that indicates the number of rows that are returned by a statement. Microsoft SQL Server does not return this message after you execute the following commands on SQL Server in the Query Analyzer utility:

   sp_configure "user options", 512
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   SET NOCOUNT ON
   GO

RESOLUTION

In Microsoft SQL Server Query Analyzer, execute the following Transact-SQL statements:

      sp_configure 'user options',0
      GO
      RECONFIGURE WITH OVERRIDE
      GO
      SET NOCOUNT OFF
      GO

Additionally, you may have to set the allow updates option. To set the allow updates option, execute these additional Transact-SQL statements:

      sp_configure 'allow updates',0
      GO
      RECONFIGURE WITH OVERRIDE
      GO

MORE INFORMATION

By default, Microsoft SQL Server returns a message that indicates the number of records that are affected by the previously executed Transact-SQL statement. The sp_configure stored procedure displays or changes global Microsoft SQL Server configuration settings. When you execute sp_configure and specify a value of 512 for the user options setting, this message is turned off:

sp_configure 'user options', 512

When you execute the following Transact-SQL statement, this message is also turned off:

SET NOCOUNT ON

REFERENCES

For more information, see Microsoft SQL Server 2000 Books Online, which is available at the following Microsoft Web site:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp