SQL table locking 1

[katoc]

Hello.

I'm using an MS Access frontend that looks at a SQL Server database. We have multiple users hitting this database through Citrix. There are two tables, Folders and Documents.

The query in question is:
SELECT Documents.*, Folders.Shelf
FROM Folders
INNER JOIN Documents ON Folders.ID = Documents.Folder_id;

We are getting a time-out. I know there is a nolock feature in SQL but how does that convert to a MS Access query?

Thanks.

Sofia

 

[jsteph]

Set the recordset type property of the query to Snapshot. That may work, if not, consider doing a pass-thru query where you can use the same With (Nolock) syntax.
--Jim

 

[Catadmin]

ISTR (and if I'm wrong, someone correct me) that MSAccess *always* will lock records in SQL when it hits the DB, regardless of locking hints. Something to do with the concurrency of data & possible updates/inserts when it's connected as a direct front end to a SQL Server DB.

You can try using the pass-through query WITH (NOLOCK), but I doubt it will work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katoc]

The snapshot solution worked. Thanks!