Solved

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

Posted on 2000-02-16
4
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error 438 6 46
Access Form - Button Format Issue 13 40
Reoccurring Access Query 24 35
Convert MS Access Excel Routine to Late Binding 6 10
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

740 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