Solved

MS Access 2003 linked to SQL Server tables, blocking lock problem

Posted on 2004-08-10
13
1,126 Views
Last Modified: 2008-02-01
Hi,

I'm having a trouble with a system that was developed by someone else originally. The system uses an Access 2003 database which links to tables in another database server. Originally the system was linking to tables in a Sybase server, but I was brought in to convert it to connect to a SQL Server 2000 server. The problem that I'm having at this point is with blocking locks on SQL Server 2000.

Locks are being placed on tables inside of a transaction each time the Update method is being called on a DAO.Recordset object. These locks will not release after this point. They persist until Access is closed. This causes a problem on any further SELECT queries ran on these tables where the blocking exclusive locks from the Recordset.Update block the SELECT statement from reading.

I've tried committing the transaction, rolling back the transaction, closing the recordset objects, setting the recordset variables to Nothing, and doing this without using transactions. None of that worked. I tried this using pessimistic locking and then using DBEngine.Idle(dbFreeLocks) and this still does not work.

Here's an example of the statement being used to open the recordset:
Set rsHRA = dbs.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges, dbPessimistic)

Originally this was being done without the dbPessimistic option, which defaults to Optimistic locking. Unfortunately I don't have the time to recode the entire system to use built SQL statements rather than Recordset objects that AddNew or Edit tables, so I must find a way to release these locks.
0
Comment
Question by:bspotswood
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11764181
You should NOT use dao against SQL server (or any other backend database server).  DAO was designed for Access.  Change your code to use ADO.

DBEngine.Idle is another Access function--it will not work with a backend datbase....
0
 
LVL 1

Author Comment

by:bspotswood
ID: 11764932
Well again, ADO isn't much of an option due to time constraints. I'm hoping there is some other option to at least release the locks with DAO. =/
0
 
LVL 34

Expert Comment

by:arbert
ID: 11765008
DAO is a hammer--SQL Server is not a nail....
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 11766611
I had the same problem with Access 2003 ADP project, Access leaves locks on tables when they are used as native Record Source for a form; I had to change to a Stored procedure as the source.

Can you change your sSql parameter to a stored procedure rather than a select statement?

Do use ADO instaed.

Access 2003 starts with ADO referenced, so it is not a problem to use.

Richard
0
 
LVL 34

Expert Comment

by:arbert
ID: 11767108
"Can you change your sSql parameter to a stored procedure rather than a select statement?

Do use ADO instaed."


Like I said above--ADO...
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11767463
Arbert correct.
Somethings can't be shortchanged and DAO against an SQL database is one of them.
You will have nothing but headaches if you continue down this path. Convert to ADO and be a hero instead of staying with DAO and be chained to this app for life!!!!!
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Expert Comment

by:arbert
ID: 11768099
(unless you're a consultant, and then maybe chained isn't a bad thing)
0
 
LVL 1

Author Comment

by:bspotswood
ID: 11768351
Well having to convert to ADO adds another conversion to my job. First, the conversion from Sybase to SQL Server, and then second the conversion from DAO to ADO. Having to add on a conversion to ADO would be best saved as a last option for my current project scope.

I find its just a shame that DAO works well enough with the access tables being linked to Sybase, but SQL Server is having this locking issue. I'm going to continue holding out for now to see if anyone else has an answer that works with DAO and linked SQL Server tables for freeing the locks.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11768433
I find it a shame that someone would use DAO to connect to ANY backend sybase or other wise...
0
 
LVL 9

Accepted Solution

by:
SoftEng007 earned 500 total points
ID: 11769595
..I find its just a shame that DAO works well enough with the access tables being linked to Sybase, but SQL Server is having this locking issue.

Chances are that if you tried to force lock contention on your sybase db using DAO you would find that Sybase is ignoring your locks! SQL Server is one of the few dbs that will attempt to issue the locks that you request using DAO.

ADO solves this problem by using a common db layer that is ODBC compliant. DAO is tricked into working with linked tables by looking at them like they are access tables.
0
 
LVL 1

Author Comment

by:bspotswood
ID: 11778951
I'm accepting SoftEng007's answer as it provided the most useful explanation. With that explanation and some demonstration recoding using ADO instead of DAO, the boss decided to okay the extra time to convert to ADO. =)
0
 
LVL 34

Expert Comment

by:arbert
ID: 11778977
"I'm accepting SoftEng007's answer as it provided the most useful explanation"

You didn't ask for an explanation of why it was happenning--you requested "so I must find a way to release these locks."
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11780262
bspotswood,
I just elaborated on arberts answer!
If you must please split the points.
In most cases when just answering a question with out solving the problem you can ask for a refund.

 Nobody answered my question. What do I do?
 http://www.experts-exchange.com/help.jsp#hi71
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now