Multi user design gaffe

[RG]

Wondering if anyone can suggest a solution to my dilemma. (VB6, ADO,
Access 200).

The situation: Because I had pre-existing data inquiry modules that
relied on database records, as well as a complex central data entry
form that uses a recordset with a number of underlying tables, I
decided to implement the form's cancel function via transaction
rollback. Probably not a great idea, but it worked well. Until of
course the client decided he wanted to expand to a multi-user setup.

I can't use optimistic locking because of the transaction context, and
record-level pessimistic locking is very problematic because of
contention for the many related tables.

I've implemented my own lock table based on the central table's unique
id, but don't seem to be able to tell the Jet engine NOT to lock the
records fetched. Are there any obvious solutions other than a fairly
complete overhaul?

[david epsom dot com dot au]

> form that uses a recordset with a number of underlying tables

don't seem to be able to tell the Jet engine NOT to lock the
records fetched.
Use a make table query to create a local table matching the recordset.

Use the local table instead of the recordset.

Just use the original query to load the local table,
and update back from the local table to the multi-user tables.

One extra local table, and a couple of lines of extra code
where selecting and saving the records.

(david)
"RG" <rg*****@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com... Wondering if anyone can suggest a solution to my dilemma. (VB6, ADO,
Access 200).

The situation: Because I had pre-existing data inquiry modules that
relied on database records, as well as a complex central data entry
form that uses a recordset with a number of underlying tables, I
decided to implement the form's cancel function via transaction
rollback. Probably not a great idea, but it worked well. Until of
course the client decided he wanted to expand to a multi-user setup.

I can't use optimistic locking because of the transaction context, and
record-level pessimistic locking is very problematic because of
contention for the many related tables.

I've implemented my own lock table based on the central table's unique
id, but don't seem to be able to tell the Jet engine NOT to lock the
records fetched. Are there any obvious solutions other than a fairly
complete overhaul?

[RG]

David, thanks for the advice. It suggests I can (indeed, have to) get
rid of the transaction calls and implement optimistic locking, with or
without maintaining the existing custom locking. Does that seem right
to you?

I'm not sure exactly what you have in place, except that the
transaction was locking all the related data tables, which
is a problem in a multi-user environment.

I thought that you might be able to leave the transactions in
place, but they would be against your local temp table.

Then after all the processing, you could update from your
local temp table to your shared table.

Using your custom locking to lock the actual target table.

Hopefully without much change at all. to your existing code.

If you can't do it without a major rewrite, then I guess you
should look for a 'best' solution rather than an 'easiest' solution

(david)
"RG" <rg*****@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

David, thanks for the advice. It suggests I can (indeed, have to) get
rid of the transaction calls and implement optimistic locking, with or
without maintaining the existing custom locking. Does that seem right
to you?

[RG]

Actually, you're right again - I wasn't thinking about applying the
transactions to a new connection for the local table, but that will
allow me to keep most of the existing code in place. Many thanks.