MS Access front end SQL 2019 Express backend. We have a linked view. Several columns from one table with about 16k records. The view is bound to a read only tabular form. All is well until a user sorts the view. When the view is sorted it places a table lock on the table and other users are locked out of the table for updates. Any ideas where to check? Thanks in advance.
Can you unlock the physical table, if you expect to do update on the table? This is the thing you should do.
Please explain. When any suer sorts the form the SQL table is locked for everyone else. How do you "unlock" it. All works fine until someone sorts the view.
There should be one lock action happened to the table when user is sorting the table/view.
Do you expect other user to be able to adjust table row/record? You can even change the way (of current form) to use one other duplicated table instead (of original table). Then Other user is able to adjust original table. But for such way, you must ensure all changes made to duplicated table should be also applied to original table.
Please explain HOW as I don't understand at all what you are explaining. When the table is locked no data can be changed.
The table is usually editable and many users are editing data. If one user sorts the SQL View then the table becomes locked for all users. This is a compiled SQL view.
Is the form using the view only?
Any other experts out there?
If you really have to sort out the table, can you copy table to another new one, and use the new one for the form?
Use a snapshot cursor in Access, instead of dynamic.
Just for testing, in Access right-click on the view and design, and add a primarykey on a unik field, and save the view.
If the problem is gone, let me know because you have to add the primary key each time you link the view in the database.
NOLOCK is only the last option in such cases.
The first thing is to use a snapshot, client side cursor.
When your issues then still occurs, then you need to look into the indices of the used tables. Are the clustered indices the appropriate ones? Do supporting or covering indices for your view exists?
Also consider using a stored procedure and a passthrough query instead.
And why displaying 16k rows to the user? I cannot work with such lists.
I am learning more all the time and did not know that. This is a seldom used search screen so users can find historical data. They can search across many columns for various criteria. I'm not worried about wrong data display as this is just a look up screen. I tried the snapshot on the form but no help.
It seems that any query I create from the view in MS Access locks the table.
The clustered PK is used in hundreds of reports and screens so design change to the underlying table is not possible.
Perhaps I can use an SP with a pass-through query. I'll give it a try or perhaps a local temp table. I appreciate your input!
How do I write this in a stored procedure? "snapshot, client side cursor".
That can lead to a considerable delay depending on the size of the result set.