TextBox preventing a set of records to update (1 Viewer)

[Hello1]

Hey guys!

I'm having a pretty weird issue. My environment is an Access frontend application and SQL Server databases as the backend.
I have a procedure which updates a set of records in a table [TableA]. It's a pretty simple update query, setting 3 attributes to 0, SQL is as follows:

UPDATE TableA
SET TableA.Saldo = 0, TableA.PredSALDO = 0, TableA.DugZaKamatu = 0
FROM TableA INNER JOIN TableB ON TableA.IdMjernogMje = TableB.IdMjernogMje
    INNER JOIN TableC ON TableB.IdSektora = TableC.IdSektora
WHERE TableC.IdMreze = @IdMreze AND TableC.IdSektora = @IdSektora

(the number of records to be updated is about 4-5 thousand)
So when I get to this part I get an ODBC Update failed error. At first I thought well maybe it is because I'm writing the SQL directly into VBA and executing it (for some reason) so I made a stored procedure on the SQL Server and I'm calling it from VBA. However, that didn't fix anything, except maybe a slight reduced time to update the records (when it does't fail). After that I found here an SQL script which supposedly returns locked tables when I run it on the SQL Server, so I did it..

SELECT
OBJECT_NAME(p.OBJECT_ID) AS TableName,
resource_type, resource_description
FROM
sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id

And it did show a few tables, including the one I'm trying to update. Next thing I did was closing one by one of the other forms to see which one is keeping the table locked. So far I found 2 of them and when I close both the UPDATE query works without any trouble.
So I took the first one to analyze it and I found out what's causing the table lock. The form is pretty much a Continuous Form with a simple query behind it of just 2 tables, however, neither of them is the locked [TableA]. I checked the on Current and on Open events and neither had any code which should cause the table lock. Anyway, in the header of the form I have 2 controls, one being a Combo Box [cboA], and the other one a Text Box. The cbo's rowsource has 4 tables, including the one which gets locked and I am using one attribute from that table in the cbo. However, the cbo is not the one causing the problem, well not directly, but the Text Box is. The text box has the following as the Control Source "[cboA].[Column](2)", it's the attribute from the [TableA] which gets locked. Once I removed that txt box the table stopped itself from locking?
Did anyone have something similar and any suggestions what could I use as a workaround to show the needed data in the cbo?
And btw, if I try to update just 1 record in the table without removing the txt box or anything while the table shows as locked it works without any problems but I didn't test at what record number exactly it starts to fails.

Thanks in advance!

 

[theDBguy]

Did anyone have something similar and any suggestions what could I use as a workaround to show the needed data in the cbo?

I can't say I have experienced that; but if you're looking for a "workaround," you could try replacing the Textbox with another Combobox with the same Row Source as the other one but display the third column instead. Just a thought...

 

[Hello1]

Thanks, will probably do so. It's just that this locked table thing pretty messy, now I will have to check for tons of forms and txt boxes to see if they are locking other or the same table.

 

[theDBguy]

Thanks, will probably do so. It's just that this locked table thing pretty messy, now I will have to check for tons of forms and txt boxes to see if they are locking other or the same table.

Good luck! Let us know how it goes.

 

[Minty]

If that combo is simply a lookup, set it's rowsource to a saved query and set the query recordset type to a snapshot, and Record locks to No Locks.
That should prevent any locking occuring.

 

[Pat Hartman]

Setting the lock properties in Access does not impact the setting in SQL Server.

Access should always make combo RowSources a snapshot. Seems like it is not doing that and so you have to get around the issue. Another option is to change the RowSource query to concatenate the two columns you want to see. That way you won't need the textbox.

 

[Shimon]

Hi,
In DB2 there is a setting of how many rows get locked at a time. I just looked this up and found that this is possible in SQL Server, as well.


There several adverse aspects in playing around with these settings without knowing what you are doing, but it may be a possible solution for your problem, if the combo box locks only certain rows and your update is to other rows.
Updating 5000 rows using row level locking will increase the memory usage of the update, as it requires the Server to register 5000 locks. I have no idea how this will effect the overall health or responsiveness of the DB.
Sincerely,
Shimon

P.S. The best solution would be as suggested already, to have the combo box use read only without locking ( or whatever this was called here). In DB2 there is a switch that can be used per query that instructs the Server to read "dirty" data and therefore does not hold a lock.