Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-10
13
Medium Priority
?
1,148 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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