Error 3218 - multi user record locking issue using transactions

[M. Arzt]

I have been bopping around the 'net for awhile
looking for answers to this.

I have a multi-user 100% Access database app w/ linked tables in a backend.
Once a month a bunch of users enter lots of data simultaneously.
The operation involves 3-4 tables.

I use DAO recordsets, standard stuff and wrap it all in a transaction
for the usual reasons. All is fine here if there is only a single
user working it.

Otherwise, we have to contend often with error 3218 - "Could not update;
currently locked"
From my reading, it appears this has to do with the fact that once the
transaction is under way, the entire (linked) table is
locked and its only released once the transaction is committed. (we are
posting new records for the most part)

I would prefer not to get rid of the transaction. I have not found
any info about how to resolve this other than trapping 3218
and offering the user to enter a timed loop that attempts
to update/commit until successful or aborted.

(I realize I might be able to use sql inserts - db.Execute - and not
have to deal with the locks, but have chosen recordsets in order
to retrieve the Autonumber primary key and use it later in the transaction)

(and I have seen suggestions that involve posting first to local temp
tables - in the front end - and then "committing" via a queries to the
backend tables - much more convoluted than I think this should be)

info on the web seems to indicate that this entire table lock might
happen only in linked tables not actual tables. one idea
I had was to open the backend db, and then the table,
instead of using CurrentDB() and therefore having to use the
linked table

Anyhow, just shooting this out to you to see if you have any other
suggestions.
All in all this seems like a silly limitation of DAO/Access, since
one would want transactions, linked tables and multi-user functionality
to all be possible.

Thanks so much to all who respond. I am not a novice so let me have your
best stuff!

[bhicks11 via AccessMonster.com]

Have you changed the default record locking settings? Is it set to No Locks?
How many users? Have you tried increasing your Update Retry Intervals or
Number of Update Retries?

Bonnie
http://www.dataplus-svc.com

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200808/1

[M. Arzt]

there are about 3 users
Default rec locks was set to No Lockes
I upped the # of retry intervals from 2 to 10...we'll see if that makes a
difference.
thanks, Bonnie

"bhicks11 via AccessMonster.com" <u44327@uwe> wrote in message
news:897277bd38e20@uwe...

[david@epsomdotcomdotau]

> All in all this seems like a silly limitation of DAO/Access, since

If it is a silly limitation of DAO/Access, it is a silly limitation
of all modern database systems: If you hold a transaction
open, nobody else can use the records, and if you hold too
many record locks, they will be upgraded to a table lock.

How many indexes do you have? You should remove all
optional indexes. If you choose to use only transactions
and VBA ISAM code, you can get rid of all the indexes.

(david)

"M. Arzt" <ma...@pacbell.net> wrote in message
news:BA6241FD-7499-4C6D...@microsoft.com...