Locking A Single Record - VB4 Prof and an Access Database 
Author Message
 Locking A Single Record - VB4 Prof and an Access Database

Visual Basic Version 4.0 Professional

I cannot lock a single record in an access database (from VB4).  I have
a small names and phone numbers database, example :-

PAUL    111 2345 634
JOHN    222 3645 234
STEVE   141 5342 752
CARL    231 1295 343

The first user (running the program from there PC) select PAUL and edit
go to the edit record form, (which sets the database record to
TABLE.EDIT)

When a second user tries to edit a record (for example STEVE) vb
crashes, with the error that the database is already being edited by
user 1.  Its as if when one user edits any record no one else can edit
one. IS THIS NORMAL???????

SOME BACKGROUND :-

Both machines are running the exact same peice of code.  The database
when opened is opened as SHARED (ie. with the first argument on the open
set to TRUE).

WHAT I AM TRYING TO ACHIEVE :-

I want VB to produce an error if someone tries to edit a record that
someone else is already editting, no other time.  When this error is
trapped i will then display the record ONLY, with a msgbox saying
'cannot edit record, already in use' (or something).  However I need it
so two (or even many more) people can edit records at the same time
(just not the same one).

I cannot believe that vb4 cannot lock records, i mean i can lock a
table, a database, or even a page (this one seemed to have no obviouse
use), but not a single record.

I am aware that its me....so can someone please put me right.

Best Regards. (And sorry this posting is so long wind'd)

Paul Reeve

(Please remove NOSPAM from email address (if it is there))
                              \|||/
                              (o o)
-------------------------OOOo--(_)--oOOO-------------------------
     ~I can barely speak for myself! let alone any body else~

-----------------------------------------------------------------



Sat, 30 Oct 1999 03:00:00 GMT  
 Locking A Single Record - VB4 Prof and an Access Database



Quote:

>>Visual Basic Version 4.0 Professional

>>I cannot lock a single record in an access database (from VB4).  I have
>>a small names and phone numbers database, example :-

>Access only locks 2K pages in your .MDB file.  With this method, you
>have no idea what is locked down.  It could be a single record, or
>multiple records in tables you don't even have open!  With access, you
>have to DEPEND on records being busy.

>>PAUL    111 2345 634
>>JOHN    222 3645 234
>>STEVE   141 5342 752
>>CARL    231 1295 343

>>The first user (running the program from there PC) select PAUL and edit
>>go to the edit record form, (which sets the database record to
>>TABLE.EDIT)

>>When a second user tries to edit a record (for example STEVE) vb
>>crashes, with the error that the database is already being edited by
>>user 1.  Its as if when one user edits any record no one else can edit
>>one. IS THIS NORMAL???????

>This is some other problem.

>>SOME BACKGROUND :-

>>Both machines are running the exact same peice of code.  The database
>>when opened is opened as SHARED (ie. with the first argument on the open
>>set to TRUE).

>>WHAT I AM TRYING TO ACHIEVE :-

>>I want VB to produce an error if someone tries to edit a record that
>>someone else is already editting, no other time.  When this error is
>>trapped i will then display the record ONLY, with a msgbox saying
>>'cannot edit record, already in use' (or something).  However I need it
>>so two (or even many more) people can edit records at the same time
>>(just not the same one).

>You can do this.  A busy error occurs if you try to access anything
>that is locked.  (Note: I believe this can include indexes, relations,
>or anything else in the database.)  You can simply trap the error and
>execute a CASE statement to figure out what is going on.

>>I cannot believe that vb4 cannot lock records, i mean i can lock a
>>table, a database, or even a page (this one seemed to have no obviouse
>>use), but not a single record.

>Nope.  There is absolutely no guarantee that you will ever be able to
>lock a single record.

>>I am aware that its me....so can someone please put me right.

>It is you.  Whats the matter with you thinking that Microsoft would
>use the simplest solution when they could instead add a layer
>of complexity!

>Jim

Thanks Jim, for replying a sheading a little light on this problem....

My next question would then be, how does other windows packages (like
inventory packages) stop two user changing the same record, without
actually stopping 2 users changing different records at one time.  I can
set a flag (i.e. A field in the record) to show that another user is
editting it, but if that users machine crashes, or just turns there pc
off, the flag stays there and no one can ever change the record.

If it appears I am now totally puzzled and dont have a clue where to go
from here, you would be right.  

Best regards.

Paul.

(Please remove NOSPAM from my email address when replying)
--
Paul Reeve



Sun, 31 Oct 1999 03:00:00 GMT  
 Locking A Single Record - VB4 Prof and an Access Database

Quote:

>Visual Basic Version 4.0 Professional
>I cannot lock a single record in an access database (from VB4).  I have
>a small names and phone numbers database, example :-

Access only locks 2K pages in your .MDB file.  With this method, you
have no idea what is locked down.  It could be a single record, or
multiple records in tables you don't even have open!  With access, you
have to DEPEND on records being busy.

Quote:
>PAUL    111 2345 634
>JOHN    222 3645 234
>STEVE   141 5342 752
>CARL    231 1295 343
>The first user (running the program from there PC) select PAUL and edit
>go to the edit record form, (which sets the database record to
>TABLE.EDIT)
>When a second user tries to edit a record (for example STEVE) vb
>crashes, with the error that the database is already being edited by
>user 1.  Its as if when one user edits any record no one else can edit
>one. IS THIS NORMAL???????

This is some other problem.

Quote:
>SOME BACKGROUND :-
>Both machines are running the exact same peice of code.  The database
>when opened is opened as SHARED (ie. with the first argument on the open
>set to TRUE).
>WHAT I AM TRYING TO ACHIEVE :-
>I want VB to produce an error if someone tries to edit a record that
>someone else is already editting, no other time.  When this error is
>trapped i will then display the record ONLY, with a msgbox saying
>'cannot edit record, already in use' (or something).  However I need it
>so two (or even many more) people can edit records at the same time
>(just not the same one).

You can do this.  A busy error occurs if you try to access anything
that is locked.  (Note: I believe this can include indexes, relations,
or anything else in the database.)  You can simply trap the error and
execute a CASE statement to figure out what is going on.

Quote:
>I cannot believe that vb4 cannot lock records, i mean i can lock a
>table, a database, or even a page (this one seemed to have no obviouse
>use), but not a single record.

Nope.  There is absolutely no guarantee that you will ever be able to
lock a single record.

Quote:
>I am aware that its me....so can someone please put me right.

It is you.  Whats the matter with you thinking that Microsoft would
use the simplest solution when they could instead add a layer
of complexity!

Jim



Mon, 01 Nov 1999 03:00:00 GMT  
 Locking A Single Record - VB4 Prof and an Access Database

On Wed, 14 May 1997 22:17:48 +0100, Paul Reeve

Quote:

>My next question would then be, how does other windows packages (like
>inventory packages) stop two user changing the same record, without
>actually stopping 2 users changing different records at one time.  I can
>set a flag (i.e. A field in the record) to show that another user is
>editting it, but if that users machine crashes, or just turns there pc
>off, the flag stays there and no one can ever change the record.

>If it appears I am now totally puzzled and dont have a clue where to go
>from here, you would be right.  

>Best regards.

This is a way:

- read the record to be edited WITHOUT locking it
- store in memory a reference copy of the record before editing it
(you can use a collection)
- when editing is done lock the record and verify that nobody else
changed it. If OK write the updated record, if not OK warn the user.

In this way locks will be very short in time and, if your users know
what they are doing, you will have no conflicts. (if they don't know
what they are doing, they will get an error message).

The JET engine can do all the work for you: look at the LockEdits
property in VB help.

Fulvio



Tue, 02 Nov 1999 03:00:00 GMT  
 Locking A Single Record - VB4 Prof and an Access Database

Hi,

Page Locking can be an annoying thing.  In the past, I have used the
following strategy to increase the multi-userability of my systems.

[1] Locate the central table that you wish to simulate single record
locking on.
[2] Add a field called 'locked' that is a simple boolean.
[3] When a client wishes to access a record, it must first check this field
to make sure it is false.  If so, grab the lock by setting the field to
true.  Use optimistic record locking and immediately free locks
(DBEngine.Idle dbFreeLocks I think).  Thus the page that this record is on
is only really locked for a minute amount of time.
[4] Keep the (software) lock as long as the user is editing the record in
the parent table, or any related records in child tables.
[5] When updating the record, use optimistic locking and immediately free
locks.
[6] Release the single record lock when the user is finished.

We have had up to ten users try to access the same record simultaneously
with this strategy with no problems.  The duration of physical page locks
is reduced by using optimistic locking and immediately freeing locks
whenever a database update is made.

Hope this helps,

Chris

BTW, locking pages is much simpler and efficient to implement then locking
single rows.  Whether this is simpler and more efficient for you is another
story ...



Quote:

> >Visual Basic Version 4.0 Professional

> >I cannot lock a single record in an access database (from VB4).  I have
> >a small names and phone numbers database, example :-

> Access only locks 2K pages in your .MDB file.  With this method, you
> have no idea what is locked down.  It could be a single record, or
> multiple records in tables you don't even have open!  With access, you
> have to DEPEND on records being busy.

[snip]


Tue, 02 Nov 1999 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. VB4- Access Database - Locking A Single Record

2. Record locked (error 3187) in single-user database.

3. Custom Locking code for Database Locked in VB4 with Access 2.0 - HELPPPPPP

4. Locked records in Access/VB4.0 16 bit

5. vb4.0 and access 2.0 record locking

6. Locked records in Access/VB4.0 16 bit

7. Record locking problems with VB-Access database

8. Record locking with ADO with Access database

9. record-locking in VB with ACCESS database

10. Record Locking Access 97 Database with ADO

11. Please Help: VB4 Locks up during database access

12. Locking a Single record on a network enviroment

 

 
Powered by phpBB® Forum Software