Solved

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

Posted on 2000-02-16
4
505 Views
Last Modified: 2006-11-17
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????
0
Comment
Question by:rknapp
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Trygve
ID: 2585510
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.
0
 
LVL 1

Author Comment

by:rknapp
ID: 2587416
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.
0
 
LVL 12

Accepted Solution

by:
Trygve earned 100 total points
ID: 2587456
Sorry about the bad attitude your recruiter is being forced upon. How do you feel yourself about future Access jobs?

Good luck!
0
 
LVL 1

Author Comment

by:rknapp
ID: 2588056
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,
RobK
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

759 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

22 Experts available now in Live!

Get 1:1 Help Now