We have a datawarehouse on SQL 2000 were users are free to query using any tools available. I have discovered that users who use Access 2002 Linked Tables and Queries hold Shared Locks on the SQL tables while they browse data. This subsequently prevents the replication task from inserting records into the table. The replication holds an Exclusive Lock which prevents subsequent new queries from querying the table. All in all, the Access user blocks queries until he or she closes the table or query window. The question is: is there any easy way to alter this behaviour in Access? Is it possible to change the ODBC or set something on the server that changes the default isolation level to read uncommitted? Or do I have to use pass through queries with (NOLOCK)???? Rgds Bertrand
Like you have said, one solution would be to use passthrough queries with NoLock but you could also provide your users with a customized form. I think that Access 2007 has also corrected this problem but I'm not sure. If your users are directly browsing the tables, then you could also try with ADP. ADP behavior is different from the regular JET in many regards but I don't know what it does against big tables when an user is directly opening a table. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Bertrand" wrote in message news:F8A9992B-1127-4F68-B41F-F267A278BAB2@microsoft.com... > We have a datawarehouse on SQL 2000 were users are free to query using any > tools available. I have discovered that users who use Access 2002 Linked > Tables > and Queries hold Shared Locks on the SQL tables while they browse data. > This > subsequently prevents the replication task from inserting records into the > table. The replication holds an Exclusive Lock which prevents subsequent > new > queries from querying the table. All in all, the Access user blocks > queries > until he or she closes the table or query window. > > The question is: is there any easy way to alter this behaviour in Access? > Is > it possible to change the ODBC or set something on the server that changes > the default isolation level to read uncommitted? Or do I have to use pass > through queries with (NOLOCK)???? > > Rgds > Bertrand >
If you use pass-through queries with NOLOCK then you're going to get dirty reads, which means that the data being browsed may never actually exist in the database. A regular pass-through query will fetch data, holding locks only as long as needed. If you're running into blocking or deadlocking issues there are two approaches you can try: 1. Fetch data in smaller batches and cache it locally. That's the wonderful thing about using ODBC-Jet is that you have the flexibility to download data so users can browse it without hitting the server every time they scroll to a new row. Narrowing your SELECT also makes sense because users can only browse so many rows at a time anyway. 2. you can use a pass-through query to execute a stored procedure. Inside the stored procedure you can have code inside of a loop that will retry a particular select if it is blocked. I'd play around with the first option of fetching less data because it's simpler -- you can create a form that lets a user define narrow search criteria and then execute the SELECT in a pass-through query. -Mary On Mon, 17 Dec 2007 02:23:00 -0800, Bertrand wrote: >We have a datawarehouse on SQL 2000 were users are free to query using any >tools available. I have discovered that users who use Access 2002 Linked >Tables >and Queries hold Shared Locks on the SQL tables while they browse data. This >subsequently prevents the replication task from inserting records into the >table. The replication holds an Exclusive Lock which prevents subsequent new >queries from querying the table. All in all, the Access user blocks queries >until he or she closes the table or query window. > >The question is: is there any easy way to alter this behaviour in Access? Is >it possible to change the ODBC or set something on the server that changes >the default isolation level to read uncommitted? Or do I have to use pass >through queries with (NOLOCK)???? > >Rgds >Bertrand
