troubleshooting Question

Pessimistic Locking with Access ADO and SQL Server

Avatar of Locky123
Locky123 asked on
Microsoft AccessMicrosoft SQL Server
6 Comments1 Solution1634 ViewsLast Modified:
Hello All

I am trying to get a definitive answer as to whether it is possible to acheive a pessimistic lock with ADO.
I am using an MS Access 2003 .mdb front end connected to SQL Server 2005.

I have a routine that creates a recordset that I need to be locked from the point of opening (no .edit command in ADO) to the point the update command is issued.

I have opened a connection using the sqloledb provider and set it's cursor locaion to adUseServer (I believe this is the default anyway)

I have then instantiated a recordset and set its 'Lock Type' to pessimistic, 'Cursor Type' to keyset and 'Cursor Location' to server side.

I have tried all manner of combinations of these parameters and I simply cannot achieve a lock.  I can open a new instance of the front end and happily update the record while to other instance supposedly has it locked.

I know that using an sp is probably the way to go, but I don't know enough T-SQL to be confident enough to produce one - and in any case, the options are there, so they should work surely!

I've been at this for three solid days now and it's driving me nuts!

Any help greatly appreciated!

Andy
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros