Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1171
  • Last Modified:

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

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
bspotswood
Asked:
bspotswood
  • 6
  • 3
  • 3
  • +1
1 Solution
 
arbertCommented:
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
 
bspotswoodAuthor Commented:
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
 
arbertCommented:
DAO is a hammer--SQL Server is not a nail....
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RichardCorrieCommented:
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
 
arbertCommented:
"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
 
SoftEng007Commented:
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
 
arbertCommented:
(unless you're a consultant, and then maybe chained isn't a bad thing)
0
 
bspotswoodAuthor Commented:
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
 
arbertCommented:
I find it a shame that someone would use DAO to connect to ANY backend sybase or other wise...
0
 
SoftEng007Commented:
..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
 
bspotswoodAuthor Commented:
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
 
arbertCommented:
"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
 
SoftEng007Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now