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:
(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..
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!
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:
SQL:
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..
SQL:
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!
Last edited: