CODEWITHSUNDEEP
ms-accessvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

MSAccess: SQL WITH (NOLOCK) vs Select Distinct?

I has an Access app with multiple users. I have a task that seems to be causing an occasional collision:

  1. Users are fetching data from a linked SQL Server table to their local DB, dirt simple:

    SELECT dbo_Item.*
FROM dbo_Item
WHERE (((dbo_Item.INVID)=4892));

  2. Some of these fetches can involve 350K+ records and take >~2 minutes to complete

  3. I currently have db.QueryTimeout = 0
  4. If two users are attempting to hit the remote table at the same time, we will occasionally see a SQL/Network error. They are NOT attempting to access the same dataset.
  5. I doubt it's a timeout issue as the ERR is thrown within 1-2 minutes

The owner of the SQL Server-side data suggested adding a (NOLOCK) hint to my Access queries. I am not seeing that as an option, but I did find a random suggestion that performing a '"SELECT DISTINCT"' would not lock the table - is this true?

Any suggestions to avoid record locking issue - or ideas what else to look for?

Thanks!

Upvotes: 2

Views: 7487

Answers (2)

Mark Pelletier
Mark Pelletier

Reputation: 1359

I decided to use a passthru SELECT query with (NOLOCK) applied. Then, I created a make_table query based on the passthru SELECT query. Seems to work just fine.

Upvotes: 1

DerekCate
DerekCate

Reputation: 306

Are they searching through 350k records or copying 350k records?

If searching through 350k records, an index on INVID may be useful.

If copying 350k record, DISTINCT could reduce this by removing duplicates, but if there are no duplicate entries, it doesn't help.

NOLOCK would help if other people were accessing the same data, essentially leaving the data open for others to modify, but your description is they are accessing different data. If from the same table, NOLOCK could help.

Upvotes: 1

Related Questions