Solved

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

Posted on 2004-08-10
13
1,141 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sorting a SQL script 5 41
SQL Server 2012 and core licensing 5 31
Rewriting a simple query 2 34
*** Windows Server 2012 Websites Set Up *** 17 32
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

751 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