Update Through Linked Table Timing Out

  • Thread starter Thread starter Smidge Boyter
  • Start date Start date
S

Smidge Boyter

I have a form that uses the default record navigation by Access. When a
user makes a change to the form and moves off the record, Access goes to
update a linked SQL Server table. I get a timeout error:
ODBC - Update on a linked tabled 'dbo_tblProductMaster' faled.
[Microsoft][ODBC SQL Server Driver] Timout expired (#0)

I used SQL Server's profiler and captured the update statement and ran it
directly on SQL Server. It times out in Query Analyzer as well. The table
has about 45 columns and the where statement on the update is passing in
every single column value in the where clause, I imagine to handle
concurrency issues.

Is there anything I can do to change the Where filter so it uses only 2
columns, primary key and a last updated date column (this is how we handle
concurrency for our other applications)? Can I override the query that
access creates and write my own by overriding an event handler?

Thanks for any advice you can give...
 
Can you add a timestamp column to the SQL Server table? I believe Access
will use one if it exists. For example, I did a quick test using a table
that has a timestamp column, and here's the result of the Profiler trace. As
you can see, the WHERE clause includes only the PK column and the timestamp
column.

exec sp_executesql N'UPDATE "dbo"."Address" SET "Address3"=@P1 WHERE
"AddressID" = @P2 AND "AddressTS" = @P3', N'@P1 nvarchar(50),@P2 int,@P3
binary(8)', N'test', 617, 0x0000000000003858

If you can't add a timestamp column to the SQL Server table, then I think
the alternative would be to use an unbound form. I don't think there's any
way you can over-ride the behaviour of a bound form.
 
Back
Top