Updatable query using 2 connections and blocking itself?

S

steveh

Hi,


we have a number of tables linked to SQL tables in an access front end.



An updatable query that comprises of a join between two of these tables
is deadlocking trying to insert a new record.


This is being caused by the fact that the select for the data is run on
a different connection to the inserts and select of @@IDENTITY and the
original select is blocking the inserts.


If we create a query based upon a single linked table then the select
and the insert run on the same connection.


Has anybody seen this and do you have any ideas as to what we could do
to resolve it?

(This is access 2003, latest ODBC drivers and SQLServer 2000 with
latest SP)
 
S

steveh

We've found the reason, our refresh code is merely putting the
FILEDSN=fred.dsn rather than the expanded contents of the filedsn(!)
Access then uses seperate connections for the select and the insert.

We're going to amend the code to expand the filedsn and this should
resolve the issue.
 
Top