By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Locking a temp table

P: n/a

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

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <co**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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

Nov 13 '05 #2

P: n/a
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:
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


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

Nov 13 '05 #3

P: n/a
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 :)

Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

P: n/a
Never really tried myself :-)

BillCo wrote:
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.


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

Nov 13 '05 #6

P: n/a
"BillCo" <co**********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
Yeah, I figured moving it to the front end would be the easiest
way alright, . . .
Temp tables do not belong in the front end, either, because they
cause bloat.

Temp tables belong in a temp file.
. . . but I don't like changing stuff on my backend . . .
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.
. . . - 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 :)


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
Nov 13 '05 #7

P: n/a
"BillCo" <co**********@gmail.com> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:
3 solutions in one go - nice one! dbDenyWrite seems to be the
cleanest way to go


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
Nov 13 '05 #8

P: n/a

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.

Nov 13 '05 #9

P: n/a
"BillCo" <co**********@gmail.com> wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:
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
It could also solve 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...
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.
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.


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
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.