Pessimistic Locking with Access ADO and SQL Server
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!