0

I'm looking for a method or solution to allow for a table to be updated that others are running select queries on?

We have an MS SQL Database storing tables which are linked through ODBC to an Access Database front-end.

We're trying to have a query run an update on one of these linked tables but often it is interrupted by users running select statements on the table to look at data though forms inside access.

Is there a way to maybe create a copy of this database table for the users to look at so that the table can still be updated?

I was thinking maybe a transaction but can you perform transactions for select statements? Do they work that way?

The error we get from inside access when we try to run the update while a user has the table open is:

enter image description here

Any help is much appreciated, Cheers

June7
  • 19,874
  • 8
  • 24
  • 34
ChufferMcDonald
  • 13
  • 5
  • Sounds like a lack of indexing and performant queries is the *real* problem. – Thom A Feb 23 '21 at 13:18
  • Also, it seems server-side cursors may being used for pagination in Access and hold long-term shared locks. I suggest you peruse this [documentation](https://learn.microsoft.com/en-us/sql/ado/guide/data/understanding-cursors-and-locks?view=sql-server-ver15). `SNAPSHOT` isolation might be a quick fix. This doesn't require an explict transaction but the SQL database must be configured to allow it. – Dan Guzman Feb 23 '21 at 13:25
  • The queries work fine if there is no blocking I'm just wondering how I can somehow split the table so that users can browse a copy of the database when they open it and then queries can run on the actual table while they browse. It's not necessary for the data to update live or anything. – ChufferMcDonald Feb 23 '21 at 13:29
  • Thanks Dan this is the kind of suggestions I was looking for. I couldn't quite find the right search term to define my issue to a search engine . – ChufferMcDonald Feb 23 '21 at 13:31
  • Sounds like this issue: https://stackoverflow.com/a/35176081/3820271 – Andre Feb 23 '21 at 13:46

1 Answers1

1

As a general rule, this should not be occurring. Those reports should not lock nor prevent the sql system from not allowing inserts.

For a quick fix, you can (should) link the reports to some sql server views for their source. And use this for the view:

SELECT * from tblHotels WITH (NOLOCK)

In fact in MOST cases this locking occurs due to combo boxes being driven by a larger table in from SQL server - if the query does not complete (and access has the nasty ability to STOP the flow of data, then you get a sql server table lock).

You also can see the above "holding" of a lock when you launch a form with a LARGE dataset If access does not finish pulling the table/query from SQL server - again a holding lock on the table can remain.

However, I as a general rule NOT seen this occur for reports.

However, it not all clear how the reports are being used and how their data sources are setup.

But, as noted, the quick fix is to create some views for the reports, and use the no-lock hint as per above. That will prevent the tables from holding locks.

Another HUGE idea? For the reports, if they often use some date range or other critera? MAKE 100% sure that sql server has index on the filter or critera. If you don't, then SQL server will scan/lock the whole table. This advice ALSO applies VERY much to say a form in which you filter - put indexing (sql server side) on those common used columns.

And in fact, the notes about the combo box above? We found that JUST adding a indexing to the sort column used in the combo box made most if not all locking issues go away.

Another fix that often works - and requires ZERO changes to the ms-access client side software?

You can change this on the server:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The above also will in most cases fix the locking issue.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Great comment thank you, after further investigation and testing we used the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and it appeared to reduce the amount of blocking that occurred on the database – ChufferMcDonald Mar 02 '21 at 17:25