How do I reload bound query/form after T-SQL update? 1

[1DMF]

Hi,

I have a problem where I have replaced a bound control with an unbound control (though the underlying query still contains the columns), I populate the unbound control with the column data from the query via the oncurrent event.

So far so good, then when the unbound control is changed I use a distributed transaction to perform an update.

However, after the T-SQL runs, the app errors with

The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time.

Now I get what the problem is, the SP has updated the column, the form has a query bound to it with this column and now there was an update outside of the form and the data held in the bound query doesn't match.

OK, so what is the correct way to deal with this, I tried using

me.requery

as I thought this would re-query the DB ignoring any changes, but this doesn't work.

I've tried

me.recalc

again without success and I assume I don't want a

me.refresh

as that would try to save the changes back to the DB, which it can't due to the above error message and the popup with Save/Copy2Clip/Drop, doesn't even have the 'save' option available as it's ghosted/disabled.

I was thinking I need to remove the column from the bound query, use a DLookup to get the initial values to populate the unbound controls and then this should remove the issue.

Is this the way to go? or is there a way to tell ms access to ignore the change to this particular column but still save changes made to other bound controls on the form.

Or is this impossible having a mixture of bound and unbound controls but have the query select the columns not bound?

Hope this makes sense, all help appreciated.
1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[lameid]

Why are you using a mix of bound and unbound?

I would use one or the other... but if you are writing based off the unbound, I think you would have to write all the changes to the bound records, "Form".Undo changes, then requery and move back to the record. That's where I'd start anyway. You might also consider the most optimistic lock settings which might be undesirable.
I guess an equivalent method would be to save the record THEN update based on the unbound control.

 

[1DMF]

Why are you using a mix of bound and unbound?

long story!

The form was entirely bound, but had some ugly legacy onchange event handler spaghetti code that was not always completing leaving serious data corruption as it might update only some of the tables it was meant to update.

So I refactored part of it to use a transaction so either it all worked or none of it, but this then threw up the issue with the 'data has changed by another user', and as there is no current development allocation to have this totally re-written to use all unbound which would take a while to re-write.

In the end I removed the fields from the bound query and use a DLookup to get initial values to populate the unbound controls, and then use the transaction to update the tables when the values are changed.

It all appears to be working fine, and the last thing I want is the old values being used and saved back to the tables in question.

It's a bit like putting a splint on a leg that really needs amputating, but without the time to perform proper surgery, this will have to suffice.

I kind of knew that mixing bound and unbound was the issue and at the time of posting I was looking for a quick fix to tell access to drop / ignore the change for the two columns in question, but I see trying to say 'update all but these columns', is not the way to go, The bound query originally had the select as 'tablename.*' , so I had to edit the bound query and manually add each column from the table except the ones I wanted to have unbound, took me a few minutes, but wasn't as painful as I first thought and has resolved the issue.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[lameid]

I'm a little surprised simply removing the offending column worked... I can see that it would but kind of assumed it was a record level contention issue rather than a particular column and record. Then again most of the linked things I have done have been upsized which would have used a timestamp column. So I'm guessing it uses timestamp to detect contention if possible otherwise all the columns which is what you are experiencing. Wasn't expecting to learn anything but I did.

 

[1DMF]

I assume that a record lock is in place on the bound form. The record lock setting for the data source is 'Edited Record'. There are also 5 tables involved in the bound query, though not all for updating. It seems that as long as the query doesn't contain the columns being updated behind the scenes via the SP transaction, it doesn't mind.

I am also curious regarding what actual lock is going on and at what level and how removing these two columns from the bound query, still allows update from two sources at a record level?

Is there such a thing as a column lock level? I'm not using NOLOCK anywhere and am using Dynaset NOT 'Dynaset (Inconsistent Updates)' on the form.

The SP starts

   -- start transaction
	BEGIN DISTRIBUTED TRANSACTION	
	SET XACT_ABORT ON;

I've not set any isolation level, so assume it runs with default 'READ COMMITTED'.

Any help you can give with understanding all this is appreciated.

1DMF.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[lameid]

I've never gone that deep in the weeds in the client server inter operation. I want to say that both database engines only do record locking. This suggests to me (almost a hunch) that Access is doing a record lock on the Access DBEngine side in this case and that it is treating the query as a data source as opposed to the underlying table. If I'm right that would mean the lock you have is session level and not database level.

To find out you would have to use appropriate SQL monitoring tools... Something I've only briefly looked at before and perhaps better suited for the SQL forum.