Record Locking Shouldn't Be So Hard 
Author Message
 Record Locking Shouldn't Be So Hard

Anyone know how to lock less than 2k of Access 2.0 data from VB?

I've always been able to lock one record at a time in an Access
environment.  VB locks a page(2k) at a time; which in this case
happens to be my entire table.

There's got to be a way to say "Hey VB, Just One Record!..
PLEASE."

Any suggestions or partial direction would be greatly appreciated.

                                                Thanks,
                                                Dave

--
db



Tue, 06 Oct 1998 03:00:00 GMT  
 Record Locking Shouldn't Be So Hard


Quote:
>Anyone know how to lock less than 2k of Access 2.0 data from VB?

... text deleted ...

Quote:

>Any suggestions or partial direction would be greatly appreciated.

>                                            Thanks,
>                                            Dave

Dave,

This is going to sound really STUPID (are you listening Microsoft!)
but, you have to add a field which signals whether the record is locked
or not.  I chose a text field filled with "Not Locked" or the user's
name. This is known as "semaphore locking".

The real problem comes when you want to lock the record. You've got to
confirm that your change to the field actually made it in after you
attempt to make the change, because two people could be writing at the
same time. You wouldn't know this until after you tried to write.

Follow up if you have more questions,
Andrew Marshall

p.s. The entry in the field must be unique to the user.
--
Andrew Marshall, Visual Basic junkie
Software Dynamics Ltd.



Wed, 07 Oct 1998 03:00:00 GMT  
 Record Locking Shouldn't Be So Hard

written:

Quote:
>I've always been able to lock one record at a time in an Access
>environment.  

I very, VERY much doubt it. Access is just the same old Jet as VB, and
it's page locking. Always has been, probably always will be.

The way to a long and happy life with Jet is not to worry desperately
over how to do record locking, but to make your app work with page
locking. It's really not that difficult.

You're trying to stop locks colliding with each other, so you need to
reduce the "cross section" of the locks. You can't make them any
"narrower" by going from pages to records, so you have to make them
"shorter" by leaving them locked for less time.

Investigate using "Optimistic" locking instead of the default
"Pessimistic" locking. You do this by setting the .LockEdits property
to false.

Quote:
>There's got to be a way to say "Hey VB, Just One Record!..

Yes, but it's tiresome. You have to implement your own table full of
little lock flags, or add a lock flag to each record (but this makes
it even more important to go optimistic).

--

"Cut the second act and the child's throat"
  - Noel Coward, on seeing the young Bonnie Langford on stage



Fri, 09 Oct 1998 03:00:00 GMT  
 Record Locking Shouldn't Be So Hard

: written:
:
: >I've always been able to lock one record at a time in an Access
: >environment.  
:
: I very, VERY much doubt it. Access is just the same old Jet as VB, and
: it's page locking. Always has been, probably always will be.
:
: The way to a long and happy life with Jet is not to worry desperately
: over how to do record locking, but to make your app work with page
: locking. It's really not that difficult.

     I have a multiuser Access/VB database doing rapid additions to
records all day long without any problems.  One thing I did was set the
LockRetrys parameter very high.  The second thing I did was to get the
users in and out of the database very quickly.  There is no need to
maintain an open connection to the database while the user is idlely
looking at or thinking about data.  I didn't open the connection until
the variable that represented the last field was entered and confirmed by
the user.  Then I opened the database, wrote the record, and closed it --
very fast, and the LockRetrys was set so high that even if there was a
lock collision, it just meant that the user waited an extra 5 seconds.  
All the multi-user writing was being done while other people were doing
fairly comlicated crosstabs on hte dataset also.  



Sat, 10 Oct 1998 03:00:00 GMT  
 Record Locking Shouldn't Be So Hard
Quote:

> Anyone know how to lock less than 2k of Access 2.0 data from VB?

> I've always been able to lock one record at a time in an Access
> environment.  VB locks a page(2k) at a time; which in this case
> happens to be my entire table.

> There's got to be a way to say "Hey VB, Just One Record!..
> PLEASE."

> Any suggestions or partial direction would be greatly appreciated.

>                                                 Thanks,
>                                                 Dave

> --
> dbA friend on mine suggested that an easy way to lock a record on loading

its data is to open it for editing at the same time as the load.
This should stop any other users loading the record except for read
only.
You can trap the error and generate a warning message for the user.
Just remember to close off that record when a new one is loaded!

Let me know if this doesn't work and I will yell at my friend.

Cheers,

Carl



Mon, 12 Oct 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Record Locking - Is there a way to test to see if a record has been locked

2. I'm having a hard time finding a record in a recordset

3. I am trying to update a record, i am not using data control

4. I am trying to update a record, i am not using data control

5. Records locks don't release - error 3260/3187

6. This shouldn't work, but ...

7. It just shouldn't be this complicated...

8. Shouldn't this be easy?

9. Forms taking focus when they shouldn't

10. Please Help: Shouldn't this work?

11. EZTouchPOS Why you shouldn't buy it

12. It shouldn't have done that...

 

 
Powered by phpBB® Forum Software