[Tutor] Re: Record Locking and Access - newbye

[Lee Harr]

>Using python dbapi I get groups of records back from a database into a list
>for example to feed a "List Form" which is just a grid on a form (the db is
>about 9000 thousand records - so I suppose there's not a big problem to
>fetch all records in a go!).
>
>Now when a user selects a record from a grid on screen I can send him to
>another form "Record Form" where he can edit the record. This way I can 
>have
>only one query to the database and have the users working locally on there
>record sets. But when a user edits an existing record I puzzled to think
>that another user might be changing that record at the same time (because
>they are not locking any records and are using lists with the record
>contents locally)! This could cause for example that user A changes record 
>1
>from "bar" to "foo" and updates the db, just after user A user B updates 
>the
>db with a change in the same record from "bar" to "foobar" (never seeing
>user As changes)!!!
>
>I guess this is a very common problem with a standard solution. How can I
>avoid this?


You say "...and Access" but it is not clear to me. Are you using a MicroSoft
Access database (ie Jet)?

In PostgreSQL, I think the standard solution to this would be to
SELECT ... FOR UPDATE  which would lock the record against other
UPDATE, DELETE, or SELECT ... FOR UPDATE transactions.
http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-FOR-UPDATE

Googling around, it is not clear to me whether MS Access has that
capability.

If not, you might have to do the locking in your application. Actually, 
either
way, your app will probably need to know something about the fact
that records might need to be locked so that it can let the user know
what is happening.

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail