Link to home
Start Free TrialLog in
Avatar of Bill Ross
Bill RossFlag for United States of America

asked on

MS Access front end SQL 2019 Express backend View locks records.

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. 

Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Can you unlock the physical table, if you expect to do update on the table? This is the thing you should do.
Avatar of Bill Ross


Hi Peter,  

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?

One form uses the view just for look up.  A different form is bound to the table.  If the form that is bound to the View is sorted the table is locked for some reason and no updates can be made.  All works OK until the view is sorted.

Any other experts out there?
Can you adjust the way? Can you sort out the view instead of the table?

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?

Avatar of ste5an
Check the kind of lock.. normally it is not a lock, but an active transaction. Some sort and scroll actions in Access use a open server-side cursor, thus you'll get blocking processes.

Use a snapshot cursor in Access, instead of dynamic.
Avatar of Bill Ross
Bill Ross
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Make sure you have a unik key on the view
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.
No. Don't use NOLOCK. It can lead to wrong data returned by your view.
Please explain.  It looks OK in testing.
NOLOCK ignores locks. Thus you can read data which is already deleted or you read uncommitted data. As it a transactional system, this means that you may read data which should not be read at this point in time. Or you when you read the view during a reorg, then you may even miss entire data pages (8k), this can mean a lot of rows.

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.
Hi ste5an,

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!
Hi ste5an,

How do I write this in a stored procedure? "snapshot, client side cursor".

Just be aware that with a snapshot cursor, you are asking for a complete copy of the result set to be made before the result is returned.

That can lead to a considerable delay depending on the size of the result set.