• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

In ADP, Form!BLAH.Recordset.LockType = adLockOptimistic, OR HELP MY BLOODPRESSURE!

Ok, I have a deliverable on the 17th.  I have 1 problem left to takle. It seems that microsoft has assumed with their new .ADP file format that all the forms should default to optimistic locking.  While this all fine and good, I ****_NEED_**** pessimistic locking.

Is there some property I can set in my connection string, or some option (There is _nothing_ in Options/Advanced about locking, I've been checking daily to see if some how something "magically" appeared.)  I've been racking my brain, and all the resource I have here, but I'm stumped.  Short of setting the recordset to an optimistically locked recordset, I don't see much in the way of a solution.  

Any Help????
  • 2
  • 2
1 Solution
This is probably way to late, but I think there is no easy way out here. Access use its own "middle-layer" when using ADODB recordsets, limiting all the nice features that ADODB gives you. One of these limitations is the locking type of recordsets. Others are the possibility of using server cursors (which makes recordsets including BLOB fields load the whole set including the BLOBs before proceeding with your code.

Some ideas/workarounds:
If you introduce timestamp fields (assuming you use SQL Server for storage) you can get a better checking for those situations where someone else has already edited the record we are trying to save. Access will use this field, if present, to check if the record has been changed on the server since it was loaded. This in conjunction with OnError event of the form (and some user training) will probably prevent data loss from these situations.

Another possible way is to use unbound forms where you load all the fields, put them into the form and then when your user presses the "Save changes" button check an UpdateDate field to see if someone else has beat you to the change. And/or introduce "CurrentlyLockedBy" and "CurrentlyLockedDateTime" fields that you set when loading the unbound form and then check this every time you enter/load a record to see if someone has "checked out" the record. This later idea can also be used on bound forms, but this will be very messy in my opinion. The same code that sets/unsets the locked fields can also check to see if there are records with locks that are more than say 20 minutes old and reset these to prevent "forever locked records". I think this is a solution that is possible to maintain for a limited number of records.
rknappAuthor Commented:
I'll take that as an answer.

It's a little late(product is now shipping!), but at least I know there is a work around. My contract here is almost up, and my recuriuter is under instructions to **NEVER** get me another access job. :)

Thanks! Submit that as an answer, and I'll accept it.
Sorry about the bad attitude your recruiter is being forced upon. How do you feel yourself about future Access jobs?

Good luck!
rknappAuthor Commented:
I'm done with access. I've been forced to use
access/vb/c++ since 94.  Now, I'm switching over
to vb/asp and maybe some php/java.....

After the 2000 release, my opinion of access sunk so low that it would have to struggle it's way up
to be considerred a bad program.

Of course, part of that may be that in the last year
I've been trying to push the envelope a lot more...

Thanks again,
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now