Locks created by Execute and CommitTran will not clear them

J

Jean

Hi

We have just recently converted a MSACCESS 97/SQL Server 7.0 app. to
MSACCESS 2002/SQL Server 2000. We have encounter some troublesome code
errors and not sure the cause. Here is one of them.

We have a table "tblMemberPaymentBatch" on SQL Server 2000 that has zero
records. It has a cluster primary key (autonumber) and an index.

Also a temporary working table on the local MDB. (not linked)
"tblwrkPaymentBatch" with primary key setup as well.

All we wanted to do is to execute an Append query (Insert into... Select
....) see below codes, but there is always locks created by the Insert on the
SQL Server that won't go away even with the CommitTrans. The locks never
get released and transaction always got roll back if we just terminate the
app.. There is no error message from the application.

I don't understand why that locks do not go away even if I included the
CommitTrans after the Execute statement.

We have tried to drop and recreated the PK and index, no help. Ran DBCC
CHECKDB, DBCC CHECKALLOC, Update Statistic, no error from the database
server either.

We tried to run the append query from query design, no locks on the
database, the locks only occur when we execute it from the script (part of a
module).

Please help!

Jean

====================================================
Function AddPayments() As Boolean
On Error GoTo AddPaymentsErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
'boolInTrans = True
'Append summary record to payment batch tbl

strSQL = "INSERT INTO tblMemberPaymentBatch ( BatchNo, " & _
" bankdate, BatchDate, PaymentTotal, BankID ) " & _
"SELECT tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([BankDate],0)) AS BDate, " & _
" NumberToDate(Nz([BankDate],0)) AS BDate2, " & _
" Sum(ConvertDollars(Nz([Payment],0))) AS Amt,
tblwrkPaymentBatch.BankID" & _
" FROM tblwrkPaymentBatch " & _
" GROUP BY tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([BankDate],0)), " & _
" NumberToDate(Nz([BankDate],0)),
tblwrkPaymentBatch.BankID "

mDB.Execute strSQL, dbSeeChanges

mWS.CommitTrans

boolInTrans = False
Call EmptyTable_TSB("", tblName)
AddPayments = True

AddPaymentsExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

AddPaymentsErr:
ErrMsgBox Err.Description, basName & ".AddPayments", Err.Number
AddPayments = False
Resume AddPaymentsExit

End Function
 
D

Dirk Goldgar

Jean said:
Hi

We have just recently converted a MSACCESS 97/SQL Server 7.0 app. to
MSACCESS 2002/SQL Server 2000. We have encounter some troublesome
code errors and not sure the cause. Here is one of them.

We have a table "tblMemberPaymentBatch" on SQL Server 2000 that has
zero records. It has a cluster primary key (autonumber) and an index.

Also a temporary working table on the local MDB. (not linked)
"tblwrkPaymentBatch" with primary key setup as well.

All we wanted to do is to execute an Append query (Insert into...
Select ...) see below codes, but there is always locks created by the
Insert on the SQL Server that won't go away even with the
CommitTrans. The locks never get released and transaction always got
roll back if we just terminate the app.. There is no error message
from the application.

I don't understand why that locks do not go away even if I included
the CommitTrans after the Execute statement.

We have tried to drop and recreated the PK and index, no help. Ran
DBCC CHECKDB, DBCC CHECKALLOC, Update Statistic, no error from the
database server either.

We tried to run the append query from query design, no locks on the
database, the locks only occur when we execute it from the script
(part of a module).

Please help!

Jean

====================================================
Function AddPayments() As Boolean
On Error GoTo AddPaymentsErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
'boolInTrans = True
'Append summary record to payment batch tbl

strSQL = "INSERT INTO tblMemberPaymentBatch ( BatchNo, " & _
" bankdate, BatchDate, PaymentTotal, BankID ) "
& _ "SELECT tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([BankDate],0)) AS BDate, " & _
" NumberToDate(Nz([BankDate],0)) AS BDate2, " &
_ " Sum(ConvertDollars(Nz([Payment],0))) AS Amt,
tblwrkPaymentBatch.BankID" & _
" FROM tblwrkPaymentBatch " & _
" GROUP BY tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([BankDate],0)), " & _
" NumberToDate(Nz([BankDate],0)),
tblwrkPaymentBatch.BankID "

mDB.Execute strSQL, dbSeeChanges

mWS.CommitTrans

boolInTrans = False
Call EmptyTable_TSB("", tblName)
AddPayments = True

AddPaymentsExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

AddPaymentsErr:
ErrMsgBox Err.Description, basName & ".AddPayments", Err.Number
AddPayments = False
Resume AddPaymentsExit

End Function
Click to expand...

Is in fact the query executing successfully? What happens if you
specify dbFailOnError instead of (or in addition to) dbSeeChanges in the
options for the Execute statement:

mDB.Execute strSQL, dbFailOnError

I'm not sure of the utility of dbSeeChanges in this context, but I'm
familiar enough with working in the SQL-linked environment to be
convinced it's wrong, either.
 
J

Jean

Dirk and all,

Thanks for the suggestion, I tried you code. An error msg comes up ask for
dbSeeChanges because it's a table with identity key.

However, I discovered the problem may be some sort of lock or unable to
access the record on the local table, tblwrkPaymentBatch on the mdb instead
of the database.

Is there a way to make sure in an mdb query between a link SQL table join
with local table will release the hold up on the local and linked tables ?

Thanks.

Jean



Dirk Goldgar said:
Jean said:
Hi

We have just recently converted a MSACCESS 97/SQL Server 7.0 app. to
MSACCESS 2002/SQL Server 2000. We have encounter some troublesome
code errors and not sure the cause. Here is one of them.

We have a table "tblMemberPaymentBatch" on SQL Server 2000 that has
zero records. It has a cluster primary key (autonumber) and an index.

Also a temporary working table on the local MDB. (not linked)
"tblwrkPaymentBatch" with primary key setup as well.

All we wanted to do is to execute an Append query (Insert into...
Select ...) see below codes, but there is always locks created by the
Insert on the SQL Server that won't go away even with the
CommitTrans. The locks never get released and transaction always got
roll back if we just terminate the app.. There is no error message
from the application.

I don't understand why that locks do not go away even if I included
the CommitTrans after the Execute statement.

We have tried to drop and recreated the PK and index, no help. Ran
DBCC CHECKDB, DBCC CHECKALLOC, Update Statistic, no error from the
database server either.

We tried to run the append query from query design, no locks on the
database, the locks only occur when we execute it from the script
(part of a module).

Please help!

Jean

====================================================
Function AddPayments() As Boolean
On Error GoTo AddPaymentsErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
'boolInTrans = True
'Append summary record to payment batch tbl

strSQL = "INSERT INTO tblMemberPaymentBatch ( BatchNo, " & _
" bankdate, BatchDate, PaymentTotal, BankID ) "
& _ "SELECT tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([BankDate],0)) AS BDate, " & _
" NumberToDate(Nz([BankDate],0)) AS BDate2, " &
_ " Sum(ConvertDollars(Nz([Payment],0))) AS Amt,
tblwrkPaymentBatch.BankID" & _
" FROM tblwrkPaymentBatch " & _
" GROUP BY tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([BankDate],0)), " & _
" NumberToDate(Nz([BankDate],0)),
tblwrkPaymentBatch.BankID "

mDB.Execute strSQL, dbSeeChanges

mWS.CommitTrans

boolInTrans = False
Call EmptyTable_TSB("", tblName)
AddPayments = True

AddPaymentsExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

AddPaymentsErr:
ErrMsgBox Err.Description, basName & ".AddPayments", Err.Number
AddPayments = False
Resume AddPaymentsExit

End Function
Click to expand...

Is in fact the query executing successfully? What happens if you
specify dbFailOnError instead of (or in addition to) dbSeeChanges in the
options for the Execute statement:

mDB.Execute strSQL, dbFailOnError

I'm not sure of the utility of dbSeeChanges in this context, but I'm
familiar enough with working in the SQL-linked environment to be
convinced it's wrong, either.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Jean said:
Dirk and all,

Thanks for the suggestion, I tried you code. An error msg comes up
ask for dbSeeChanges because it's a table with identity key.
Click to expand...

Did you try combining the two options? E.g,

mDB.Execute strSQL, dbSeeChanges + dbFailOnError
However, I discovered the problem may be some sort of lock or unable
to access the record on the local table, tblwrkPaymentBatch on the
mdb instead of the database.

Is there a way to make sure in an mdb query between a link SQL table
join with local table will release the hold up on the local and
linked tables ?
Click to expand...

That's too vague for me, I'm afraid. Why is there a lock on
tblwrkPaymentBatch? What is the context in which your AddPayments
routine is called? For that matter, where is the variable mDB declared,
and why have you commented out the line
'boolInTrans = True
Click to expand...

? My guess is that was for testing, but I think it should be restored.
 
J

Jean

Dirk,

Let me add more details.

We are using the data from tblwrkPaymentBatch (local table) to create record
onto separate linked SQL tables.
There are three append queries (Insert into SQLtables.... Select .... from
tblwrkPaymentBatch ) one delete query.

In two of the queries (type Append and Delete), tblwrkPaymentBatch is joined
with a SQL table that has 700 records. These two queries notifies exceptions
on data, and they always run first before the others. They are ran in
workspace and begintrans, commitran statements is prepared. There is no lock
created on SQL server afer these executions. Records were inserted into
Exception table and deleted from tblwrkPaymentBatch. See below Actual Query
SQL code.

Exception Quries
----------------------------------------------------------------------------
--------
INSERT INTO tblMemberPaymentImportExceptions ( BatchID, BatchDate, MemberID,
MemberName, AmountPaid, ExceptDate )
SELECT tblwrkPaymentBatch.BatchNo, tblwrkPaymentBatch.ConvDate AS BDate,
tblwrkPaymentBatch.MemberNo, tblwrkPaymentBatch.MemberName,
ConvertDollars(Nz([Payment],0)) AS Amt, Now() AS XDate
FROM tblwrkPaymentBatch LEFT JOIN tblMembers ON tblwrkPaymentBatch.MemberNo
= tblMembers.MemberID
WHERE (((tblMembers.MemberID) Is Null));
----------------------------------------------------------------------------
-------------------
DELETE tblwrkPaymentBatch.*, tblMembers.MemberID
FROM tblwrkPaymentBatch LEFT JOIN tblMembers ON tblwrkPaymentBatch.MemberNo
= tblMembers.MemberID
WHERE (((tblMembers.MemberID) Is Null));
----------------------------------------------------------------------------
---------------------

Next, I noticed the locks starts to appear when execute the other append
quries (see below) and afterward commitran will not clear the locks...





----------------------------------------------------------------------------
-------------------------
INSERT INTO tblMemberPaymentBatch ( BatchNo, bankdate, BatchDate,
PaymentTotal, BankID )
SELECT tblwrkPaymentBatch.BatchNo, NumberToDate(Nz([BankDate],0)) AS BDate,
NumberToDate(Nz([BankDate],0)) AS BDate2,
Sum(ConvertDollars(Nz([Payment],0))) AS Amt, tblwrkPaymentBatch.BankID
FROM tblwrkPaymentBatch
GROUP BY tblwrkPaymentBatch.BatchNo, NumberToDate(Nz([BankDate],0)),
NumberToDate(Nz([BankDate],0)), tblwrkPaymentBatch.BankID;
----------------------------------------------------------------------------
--------------
INSERT INTO tblMemberPayments ( BatchID, BankDate, BatchDate, MemberID,
AmountPaid, BankID )
SELECT tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([tblwrkPaymentBatch]![BankDate],0)) AS BDate,
NumberToDate(Nz([tblwrkPaymentBatch]![BankDate],0)) AS BDate2,
tblwrkPaymentBatch.MemberNo, (ConvertDollars(Nz([Payment],0))) AS Amt,
tblwrkPaymentBatch.BankID
FROM tblwrkPaymentBatch;
----------------------------------------------------------------------------
-----------------

BUT... the interesting part

If I do not execute the Exception queries above, the later append queries
will run just fine, the committran WILL clear the locks. And also, if I
execute the Exception queries AFTER the other queries instead, no lock as
well.

This leads me to wonder, it seems like th Exception queries some how made
the records on the local tblwrkPaymentBatch locked on mdb and I am not sure
now to release them for the later append quries.

What do you think ?

Thanks
Jean
 
D

Dirk Goldgar

Jean said:
Dirk,

Let me add more details.

We are using the data from tblwrkPaymentBatch (local table) to create
record onto separate linked SQL tables.
There are three append queries (Insert into SQLtables.... Select ....
from tblwrkPaymentBatch ) one delete query.

In two of the queries (type Append and Delete), tblwrkPaymentBatch is
joined with a SQL table that has 700 records. These two queries
notifies exceptions on data, and they always run first before the
others. They are ran in workspace and begintrans, commitran
statements is prepared. There is no lock created on SQL server afer
these executions. Records were inserted into Exception table and
deleted from tblwrkPaymentBatch. See below Actual Query SQL code.

Exception Quries
---------------------------------------------------------------------- ------
--------
INSERT INTO tblMemberPaymentImportExceptions ( BatchID, BatchDate,
MemberID, MemberName, AmountPaid, ExceptDate )
SELECT tblwrkPaymentBatch.BatchNo, tblwrkPaymentBatch.ConvDate AS
BDate, tblwrkPaymentBatch.MemberNo, tblwrkPaymentBatch.MemberName,
ConvertDollars(Nz([Payment],0)) AS Amt, Now() AS XDate
FROM tblwrkPaymentBatch LEFT JOIN tblMembers ON
tblwrkPaymentBatch.MemberNo = tblMembers.MemberID
WHERE (((tblMembers.MemberID) Is Null));
---------------------------------------------------------------------- ------
-------------------
DELETE tblwrkPaymentBatch.*, tblMembers.MemberID
FROM tblwrkPaymentBatch LEFT JOIN tblMembers ON
tblwrkPaymentBatch.MemberNo = tblMembers.MemberID
WHERE (((tblMembers.MemberID) Is Null));
---------------------------------------------------------------------- ------
---------------------

Next, I noticed the locks starts to appear when execute the other
append quries (see below) and afterward commitran will not clear the
locks...





---------------------------------------------------------------------- ------
-------------------------
INSERT INTO tblMemberPaymentBatch ( BatchNo, bankdate, BatchDate,
PaymentTotal, BankID )
SELECT tblwrkPaymentBatch.BatchNo, NumberToDate(Nz([BankDate],0)) AS
BDate, NumberToDate(Nz([BankDate],0)) AS BDate2,
Sum(ConvertDollars(Nz([Payment],0))) AS Amt, tblwrkPaymentBatch.BankID
FROM tblwrkPaymentBatch
GROUP BY tblwrkPaymentBatch.BatchNo, NumberToDate(Nz([BankDate],0)),
NumberToDate(Nz([BankDate],0)), tblwrkPaymentBatch.BankID;
---------------------------------------------------------------------- ------
--------------
INSERT INTO tblMemberPayments ( BatchID, BankDate, BatchDate,
MemberID, AmountPaid, BankID )
SELECT tblwrkPaymentBatch.BatchNo,
NumberToDate(Nz([tblwrkPaymentBatch]![BankDate],0)) AS BDate,
NumberToDate(Nz([tblwrkPaymentBatch]![BankDate],0)) AS BDate2,
tblwrkPaymentBatch.MemberNo, (ConvertDollars(Nz([Payment],0))) AS Amt,
tblwrkPaymentBatch.BankID
FROM tblwrkPaymentBatch;
---------------------------------------------------------------------- ------
-----------------

BUT... the interesting part

If I do not execute the Exception queries above, the later append
queries will run just fine, the committran WILL clear the locks. And
also, if I execute the Exception queries AFTER the other queries
instead, no lock as well.

This leads me to wonder, it seems like th Exception queries some how
made the records on the local tblwrkPaymentBatch locked on mdb and I
am not sure now to release them for the later append quries.

What do you think ?
Click to expand...

I think, unfortunately, you're getting beyond my area of expertise and
may want to start a new thread in hopes of getting someone more
knowledgeable about SQL Server on the line. I'm only a dabbler in SQL
Server, at this point, so anything to do with SQL Server itself is
likely to go right by me . I'm willing to keep pursuing this with you
from the Access side, though, if you're game.

How are you establishing what locks SQL Server is creating?

If I'm to spot something in the code you're running in Access, you're
going to have to show me, not just the queries, but the code sequences
in which they are called. At the moment, the only odd thing I see about
the queries is the inclusion of tblMembers.MemberID in the field list of
the delete query. I would expect that query to look like either this:

DELETE tblwrkPaymentBatch.*
FROM tblwrkPaymentBatch
LEFT JOIN tblMembers
ON tblwrkPaymentBatch.MemberNo = tblMembers.MemberID
WHERE (((tblMembers.MemberID) Is Null));

or just this:

DELETE
FROM tblwrkPaymentBatch
LEFT JOIN tblMembers
ON tblwrkPaymentBatch.MemberNo = tblMembers.MemberID
WHERE (((tblMembers.MemberID) Is Null));

If I run a query like your original from the query designer, it fails
with error 3086, "Could not delete from the specified tables". At
least, that's what it does for me, working locally in Access. But if
the query failed, shouldn't your code have trapped that error? Hmm ...
I can't tell whether this is related to your problem or not.
 
T

TC

(snip)
Did you try combining the two options? E.g,

mDB.Execute strSQL, dbSeeChanges + dbFailOnError
Click to expand...


Should that not be OR (not +) (regardless of online help)?

TC
 
T

TC

Dirk Goldgar said:
I don't think it needs to be.

?dbSeeChanges
512
?dbFailOnError
128
?dbSeeChanges + dbFailOnError
640
?dbSeeChanges OR dbFailOnError
640

Powers of 2.
Click to expand...

Ok, looks good! But I still feel unconfortable with +. Given the choice, I
think I'll stick with OR. otheriwse I might start using + in cases where the
constants were large enough to cause a problem :)

TC
 
D

Dirk Goldgar

TC said:
(snip)



Should that not be OR (not +) (regardless of online help)?
Click to expand...

I don't think it needs to be.

?dbSeeChanges
512
?dbFailOnError
128
?dbSeeChanges + dbFailOnError
640
?dbSeeChanges OR dbFailOnError
640

Powers of 2.
 
M

Marshall Barton

TC said:
Ok, looks good! But I still feel unconfortable with +. Given the choice, I
think I'll stick with OR. otheriwse I might start using + in cases where the
constants were large enough to cause a problem :)
Click to expand...


I agree that OR means more to me than Plus for this kind of
thing in VBA, but that's just my assembly background showing
through. BUT, be careful that you don't try to use OR in a
context where it is just a logical operator and does not do
a bitwise operation. E.g. SQL and control source
expressions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is it possible to include Begin And End Transaction code in VBA ? 6
Duplicate Subform and SubSubForm Records but not Main 1
DAO operation issues runtime err3003: could not start transaction 2

Top