Keep being told table is locked by another user when noone else is using it

[RECrerar]

Hi,

I'm not too sure what I've done. I'm trying to run a make table query on my database but keep getting the error message that the table is locked becuase it is in use by another user. This is not the case - noone else is currently using the database and I do not have the table open.

I decided to try and see if by chance people were playing around with the database and used the tool on this link
http://www.access-programmers.co.uk/forums/showthread.php?t=155950&highlight=View+Users

When I ran the tool I got this error:

Hmm okay that is interesting, I just tried to run the tool again to reproduce the error to put in this thread, I ran it with the database shut and it tells me I am using it. The database is not open.

Am going to try resetting the computer and see if that fixes the problem. If anyone has seen anything like this before please do let me know.

Regards
Robyn

 

[ajetrumpet]

ideas:

1) compact and repair the DB
2) make sure there is only one object open when you are trying to do this
3) close it and restart your computer
4) open in exclusive mode and try the same thing

 

[RECrerar]

Hi thanks for the reply.

I've discovered what's causing it. The query is called as part of a loop as the same report needs to be generated for two different business areas. because I just had the report coming up as acWindowNormal the code was trying to generate the second report when the first one was still open and using the table, by changing this to acDialog I have now fixed the problem, although I will now have to inform the users they can not do anything more with the database until they shut the report.

 

[RECrerar]

Oh actually that's not going to work as a solution as then the user can't do anything with the file such as print it, export it etc.

I could put some code in the onCLose event of the report to automatically trigger the generation of the second one but that all seems a bit like over design to me. I think what I'll do is just only allow them to generate one report at a time (there are after all only two of them) and then put some code in that will check that they have closed the report before allowing exectution of the second one.

Just thought I should write what I've done incase any one else get's stuck.

 

[The_Doc_Man]

You are absolutely correct to post your solution! Thank you for an intelligent and thoughtful approach to problem solving. After all, a solution not shared is someone else's problem not solved.

 

[gemma-the-husky]

what you can do is

a) open the first form/report then
b) wait for this to close BEFORE opening the second form/report

i do this regularly with this function

docmd.openform "forma"
while isopen("forma",acform) 'for report use acreport - acform is exoected and is optional
   doevents
wend
docmd.openform "formb"

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
    IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

 

[RECrerar]

Oh that sounds like a nice solution.

I take it then that using this the user would be able to chose to print the report and whatever while the doevents loop is running.

I think I would need a message box up to inform them that they have to shut the report before the next one will be generated and maybe set a timer that pops up a reminder if they don't shut it within a certain time. Else I can imagine them just staring at the screen for ages wondering why nothing is happening.

However that definately has potential, I think I may try and incorporate it. Thanks.

Robyn