Solved

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

Posted on 2004-08-10
13
1,125 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 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

17 Experts available now in Live!

Get 1:1 Help Now