Locking a temp table

I have a function which clears out a temporary table and then fills it
with values from a recordset. One of the table fields is indexed with
no duplicates. Very rarely - almost enough to ignore it - two users
will call the function at once causing one of the functions to attempt
to update the table with duplicate values.

Is there a way to lock the table to prevent this happening?

Function()
'wait until tmpTable is free
'lock entire tmpTable here
delete * from tmpTable
do while recordset
Update table![Indexed Field]
loop
'Release table here?
End function

Thanks

re: Locking a temp table

The easiest way around this would be to not place the temp table in the
back-end file. Instead place it in the front-end file where each user has
their own copy of the temp table. An even better way to handle the situation
is to create a temporary file to place the temp table in. This will keep the
bloat caused by filling and emptying the table to a minimum.

For more information, see this link:
http://www.granite.ab.ca/access/bloatfe.htm

--
Wayne Morgan
MS Access MVP


"BillCo" <coleman.bill@gmail.com> wrote in message
news:1119949861.758056.140680@g47g2000cwa.googlegr oups.com...[color=blue]
>
> I have a function which clears out a temporary table and then fills it
> with values from a recordset. One of the table fields is indexed with
> no duplicates. Very rarely - almost enough to ignore it - two users
> will call the function at once causing one of the functions to attempt
> to update the table with duplicate values.
>
> Is there a way to lock the table to prevent this happening?
>
> Function()
> 'wait until tmpTable is free
> 'lock entire tmpTable here
> delete * from tmpTable
> do while recordset
> Update table![Indexed Field]
> loop
> 'Release table here?
> End function
>
> Thanks
>[/color]

re: Locking a temp table

Evade the problem completely by including a [user] field in the table?

Store a semaphore value somewhere in the same database? If a record
exists in table [lock], don't execute the procedure but wait

use the dbDenyWrite option for OpenRecordset?

BillCo wrote:
[color=blue]
> I have a function which clears out a temporary table and then fills it
> with values from a recordset. One of the table fields is indexed with
> no duplicates. Very rarely - almost enough to ignore it - two users
> will call the function at once causing one of the functions to attempt
> to update the table with duplicate values.
>
> Is there a way to lock the table to prevent this happening?
>
> Function()
> 'wait until tmpTable is free
> 'lock entire tmpTable here
> delete * from tmpTable
> do while recordset
> Update table![Indexed Field]
> loop
> 'Release table here?
> End function
>
> Thanks
>[/color]

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

re: Locking a temp table

Yeah, I figured moving it to the front end would be the easiest way
alright, but I don't like changing stuff on my backend - it make
accessing monthend archives that bit more of an ordeal. Also I kinda
got stuck trying to find a code solution... it's become an question of
academic interest now! Thanks for your help though :)

re: Locking a temp table

3 solutions in one go - nice one! dbDenyWrite seems to be the cleanest
way to go... I'm thinking something like this would work?

dim tbl as tabledef
set tbl = db.tabledefs![tmpTable]
Do While not (tbl.updateable)
call sleep(100)
loop
set rst = db.openrecordset("tmpTable", dbDenyWrite)
'update
'close rst
'etc.

re: Locking a temp table

Never really tried myself :-)

BillCo wrote:
[color=blue]
> 3 solutions in one go - nice one! dbDenyWrite seems to be the cleanest
> way to go... I'm thinking something like this would work?
>
> dim tbl as tabledef
> set tbl = db.tabledefs![tmpTable]
> Do While not (tbl.updateable)
> call sleep(100)
> loop
> set rst = db.openrecordset("tmpTable", dbDenyWrite)
> 'update
> 'close rst
> 'etc.
>[/color]

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

re: Locking a temp table

"BillCo" <coleman.bill@gmail.com> wrote in
news:1119956061.974189.182000@o13g2000cwo.googlegr oups.com:
[color=blue]
> Yeah, I figured moving it to the front end would be the easiest
> way alright, . . .[/color]

Temp tables do not belong in the front end, either, because they
cause bloat.

Temp tables belong in a temp file.
[color=blue]
> . . . but I don't like changing stuff on my backend . . .[/color]

Your back end is designed wrong. What you described is putting data
that is specific to a particular user in the shared data file, and
that makes no sense.
[color=blue]
> . . . - it
> make accessing monthend archives that bit more of an ordeal. Also
> I kinda got stuck trying to find a code solution... it's become an
> question of academic interest now! Thanks for your help though :)[/color]

Temp tables shouldn't need to be archived, so why should it matter?

Maybe it's not a temp table at all (i.e., a work table that you load
with data that is discarded after the work is done).

Maybe you're using writing records to a table instead of selective
querying of a larger dataset.

Sounds like a design error, either way.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Locking a temp table

"BillCo" <coleman.bill@gmail.com> wrote in
news:1119964911.350438.39790@z14g2000cwz.googlegro ups.com:
[color=blue]
> 3 solutions in one go - nice one! dbDenyWrite seems to be the
> cleanest way to go[/color]

Well, it may answer the question you *asked*, but I think your
question has revealed an error in your design.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Locking a temp table

1. I'm the 4th person to work on this database which has been around
for a long and grown organically for 5 years before I even joined this
company.
2. Re-designing the backend now could cause major problems
3. I agree that temp tables do not belong in the backend - but temp vs
perm tables is not black and white ...there's a grey area. How long it
is needed, how global is the data, who runs subsequent queries from
it...
4. Creating temporary a temporary database, building a table, adding
records to the table and then deleting the database adds a time
overhead can be too much and is not practical in ALL circumstances.

re: Locking a temp table

"BillCo" <coleman.bill@gmail.com> wrote in
news:1120117783.986559.26980@g44g2000cwa.googlegro ups.com:
[color=blue]
> 1. I'm the 4th person to work on this database which has been
> around for a long and grown organically for 5 years before I even
> joined this company.
> 2. Re-designing the backend now could cause major problems[/color]

It could also solve major problems.
[color=blue]
> 3. I agree that temp tables do not belong in the backend - but
> temp vs perm tables is not black and white ...there's a grey area.
> How long it is needed, how global is the data, who runs subsequent
> queries from it...[/color]

I don't see to many gray areas. Temp data is temp data. It doesn't
belong in the shared back end, unless multiple users are sharing the
same temp data. In that case, it's a different kind of application
than I've ever worked with.
[color=blue]
> 4. Creating temporary a temporary database, building a table,
> adding records to the table and then deleting the database adds a
> time overhead can be too much and is not practical in ALL
> circumstances.[/color]

Who recommended this latter course of action?

I create the temp MDB once, and leave it there.

If I'm concerned with bloat, I kill it on exit from the application,
and copy an empty copy of it from the server to the workstation on
application startup.

However, you'd be surprised how quickly Jet can create a new MDB
file -- it doesn't take nearly as long as you might suspect. I don't
do it because I just don't do DDL or that kind of DAO -- I much
prefer the two lines of code my solution takes.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc