ODBC and Deadlock

[merco]

hi,
i'm using linked table from access 2k to sql2k.

In a form delete event i do an update (rs.update and then rs.close) to
another table (let's call it "X") not the same where the form is linked
to.

I get a deadlock to the table "X" for those modified records.
I discovered that until i am in the delete event (debug mode) i can't
select records from table "X" neither from a Query analizer SQL.

Why this behaviour ?

[merco]

it seems that all the code within the delete event belong to an
implicit Transaction (made from Access i suppose).

Maybe this is due to the "cancel" parameter that is required from this
kind of event procedure ?

[merco]

it seems that all the code within the delete event belong to an
implicit Transaction (made from Access i suppose).

Maybe this is due to the "cancel" parameter that is required from this
kind of event procedure ?

[david epsom dot com dot au]

Sometime in the last 6 years, Jet transactions
were changed to prevent 'dirty reads'. You
can't read anything touched inside a transaction.

Access uses a collection of cached connections,
and each connection runs a separate transaction(!)

After you have touched the records, none of the
other connections can read the records until
the transaction is completed.

You have no control over which connection you
use, so you will always get spurious locks in
transactions.

Solutions are
(1) upgrade to A97/ Jet 3.51, which does not exhibit
this behaviour.

or
(2) use a different workspace, so that your database
action is not inside the implicit transaction.

set ws = application.dbengine.createworkspace
set db = ws.opendatabase(codedb.name)

(david)

"merco" <d.********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

it seems that all the code within the delete event belong to an
implicit Transaction (made from Access i suppose).

Maybe this is due to the "cancel" parameter that is required from this
kind of event procedure ?

[Rich P]

Just my 2¢ worth, but when ever you are trying to manipulate data in a
Sql Server database through ODBC, more times than not - it leads to
complications such as what you are describing. A more reliable
technique for manipulating Sql Server database is to use ADO.

Ex:

Sub DelRecs()
Dim cmd As New ADODB.Command
cmd.ActiveConnection = &
"Provider=SQLOLEDB;Source=YourSqlServer;Database=Y ourSqlDatabase;UID=Ste
ve;PWD=Secret;"
cmd.TimeOut = 600 '--seconds that is - 600 seconds
'--- 10 minutes - for safety
cmd.CommandType = adCmdText
cmd.ComandText = _ &
"Delete From yourTable Where somefield = something"
cmd.Execute
cmd.ActiveConnection.Close
End Sub

This is way faster than doing it through ODBC, ODBC passthrough query.
The likelyhood of having contention issues using ADODB is greatly
reduced.

Rich

*** Sent via Developersdex http://www.developersdex.com ***