Solved

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

Posted on 2000-02-16
4
526 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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