Table Locking Problem

[Joe Holzhauer]

Hi, there!

I have an employee evaluation program I'm writing, and I've hit a snag:

I have a tblRatings to store all the employee ratings (by item evaluated,
employee, evaluator, & evaluation). With several evaluations a year, ~100
employees, ~20 evaluators, and ~250 items to be evaluated, the table is
pretty big. In order to maximize program speed, I generate a temporary
table of only the items that pertain to the current evaluator, employee, and
evaluation. This way I'm dealing with 250 records instead of >1 million.

So, the evaluator enters the evaluation into tblTempRatings. When finished,
they select a new employee...

In the cboEmployeeID_BeforeUpdate event, assuming the user wants to save
changes, I run an update query with DoCmd.RunSQL to copy the temporary
ratings back into the big table. Then, I clear out the temp table:

DoCmd.RunSQL "DELETE * FROM tblTempRatings"

After that (in the cboEmployeeID_AfterUpdate event), I try to re-create the
temporary table for the newly selected employee:

DoCmd.RunSQL "SELECT ... INTO ..."

But I get an error at that point saying the db couldn't lock the table
because it's in use by another person or process. I'm not sure what I'm
missing.

Any ideas?

Thanks!
Joe

 

[t t via AccessMonster.com]

the problem may be that you are trying to make a new table allthough it is
present.


DoCmd.RunSQL "DELETE * from tbltabletemp"

after clearing the template table try to append records on it.

DoCmd.RunSQL "INSERT INTO tbltabletemp SELECT * FROM sourcetable
where (((sourcetable.id) Like [input id]))"

I hope this will help you

 

[t t via AccessMonster.com]

sorry second sql string will be as ;


DoCmd.RunSQL "INSERT INTO tbltabletemp SELECT * FROM sourcetable WHERE
sourcetable.id like [input id]"