ODBC TIMEOUT ON UPDATE

[BillyBob]

Hi,

Our office uses Access 2000 as a front end to a SQL
server 2000 database.
Each client has it's own copy of the access .mdb file on
their computer.

It has been up and running for about a year and half but
recently have been getting sporatic ODBC timeout errors.

The syntax of the error is:
ODBC -- update on a linked table 'tableName' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

It only seems to happen in one particular Form when a
user adds/updates a record. The table underneath is a
linked table which has probably 15,000 records but the
form only pulls about 1000 records at a maximum for users
to edit/add/update. The timeout occurs if only 3 records
have been pulled to the form or 1000 but is very sporadic.

The table only 9 columns.

I can't for the life of figure it out.

Thanks

 

[Raghu Prakash]

Hi BillyBob,

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

For Further Information : Microsoft Knowledge Base Article - 305617
http://support.microsoft.com/default.aspx?scid=kb;en-us;305617

Thank You...
Raghu...
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[BillyBob]

Thanks Raghu,
I'll give it a try later on today to see if it has any
effect.

My only concearn though is that this problem happens so
randomly (the add/updates usually work) and the fix that
you listed looks like it is either: yes, they will always
work based on the SQL server settings or they won't.

I've never executed the commands:
sp_configure "user options", 512
GO
RECONFIGURE WITH OVERRIDE
GO
SET NOCOUNT ON
GO
and if it isn't set that way be default AND based
on the fact that this form and it's underlying table
have been working correctly for over a year and a half
with no ODBC timeout errors, I don't think that this is
going to solve the problem. But I will try it just to
make sure.

Thanks again and if you or anybody else has any
suggestions, I'd really appreciate it.

BillyBob!