lock table

[wailingrecluse]

Guys,

I'm having trouble with some code.

DoCmd.SetWarnings False
DoCmd.Close acForm, "TblTotalPorts", acSaveNo
DoCmd.OpenForm "frmReload"
DoCmd.OpenQuery "QMakeDump", acNormal, acEdit
DoCmd.OpenQuery "QPorts", acNormal, acEdit
DoCmd.OpenQuery "QPortCount", acNormal, acEdit
DoCmd.OpenForm "TblTotalPorts"

Pretty simple, the form should close, a form with a loading progress bar should appear while the queries run. This form should then close and the original form should re-open with refreshed data.

But, when it gets to line

DoCmd.OpenQuery "QPortCount", acNormal, acEdit

I receive the error

Runtime error 3211. The database engine could not lock table 'TblTotalPorts' because it is already in use by another person or process.

Seems that something is holding on to the table, but I can't figure out what.

If I comment that line out, it runs great, doing everything else, but obviously I need that line to actually refresh the data.

Thanks,

Richard

 

[boblarson]

What type of queries are these (make table, append, delete, update?)

 

[wailingrecluse]

What type of queries are these (make table, append, delete, update?)

Hi

They're all make table queries.

Richard

 

[boblarson]

Hi

They're all make table queries.

Richard

My suggestion? Don't use Make Table queries. In fact, use them as little as is humanly possible.

Just create the table once, then run a delete query to delete the data and then use an Append query to append the new data. It will also solve your issue.

 

[wailingrecluse]

My suggestion? Don't use Make Table queries. In fact, use them as little as is humanly possible.

Just create the table once, then run a delete query to delete the data and then use an Append query to append the new data. It will also solve your issue.

Hi there

Tried your suggestion and I now get the following error:

Runtime error 3008. The table 'TblTotalPorts' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

Any more ideas?

Richard

 

[boblarson]

Is frmReload bound to the table?

 

[wailingrecluse]

Is frmReload bound to the table?

Hi

No it's not, but form TblTotalPorts is bound to the table.

I thought that might cause the problem, but I've written a line to close the form before anything else.. yet it still seems to be keeping a link there.

R

 

[boblarson]

Try adding

DoEvents

just after your code which Closes the form.

 

[wailingrecluse]

Try adding

DoEvents

just after your code which Closes the form.

Hi

The code now looks like this:

Private Sub Form_Timer()
DoCmd.SetWarnings False
DoCmd.Close acForm, "TblTotalPorts", acSaveNo
DoEvents
DoCmd.OpenForm "frmReload"
DoCmd.OpenQuery "QMakeDump", acNormal, acEdit
DoCmd.OpenQuery "QPorts", acNormal, acEdit
DoCmd.OpenQuery "QDeleteTotalPorts", acNormal, acEdit
DoCmd.OpenQuery "QPortCount", acNormal, acEdit
DoCmd.OpenForm "TblTotalPorts"
End Sub

But the error message is still the same

Richard

 

[boblarson]

Could you upload a copy of the database (with bogus data, of course) so we can try a few things out?

 

[wailingrecluse]

Could you upload a copy of the database (with bogus data, of course) so we can try a few things out?

Hi Bob

I'd be happy to, but it won't work because the first query QMakeDump, makes a table from a linked table. This is to avoid running queries across live tables over the network.

if you run that query you won't get anything, and that would affect the rest of the queries.

I could remove the link table and leave you with the dump table, but I'd need to manipulate a massive amount of data for you to be able to do anything.

I'll keep messing around with it... I appreciate all your efforts

Richard

 

[boblarson]

So you still have a make table query in there? I thought you were going to change it to APPEND records not make the table.

Also, if you remain stuck, you might make a local copy of the table (definitions only) and then append a small subset of data, delete the linked one and then rename the new one to the old name. (make sure Name AutoCorrupt - I mean Name AutoCorrect is turned off - which it should be anyway).

Then you could upload that (after running compact and repair and then ZIPPING the file).

EDIT: And I mean on a COPY.

 

[wailingrecluse]

So you still have a make table query in there? I thought you were going to change it to APPEND records not make the table.

Also, if you remain stuck, you might make a local copy of the table (definitions only) and then append a small subset of data, delete the linked one and then rename the new one to the old name. (make sure Name AutoCorrupt - I mean Name AutoCorrect is turned off - which it should be anyway).

Then you could upload that (after running compact and repair and then ZIPPING the file).

EDIT: And I mean on a COPY.

The only make table query is the QMakeDump, which pulls data (>=Date*() ) into a new table to avoid querying the linked table across the network.

Do you suggest changing that query also to append query?

 

[boblarson]

Do you suggest changing that query also to append query?

YES, YES, YES - because that is likely the source of your locking problem. If a table is only deleting and receiving records then there is no problem for the other stuff to work because the structure is not in use. As long as you don't have record locking set - you have the defaults of NO LOCKS and OPEN DATABASES WITH RECORD LEVEL LOCKING checked in the database options, you shouldn't have a problem with deletions and appending.

 

[wailingrecluse]

YES, YES, YES - because that is likely the source of your locking problem. If a table is only deleting and receiving records then there is no problem for the other stuff to work because the structure is not in use. As long as you don't have record locking set - you have the defaults of NO LOCKS and OPEN DATABASES WITH RECORD LEVEL LOCKING checked in the database options, you shouldn't have a problem with deletions and appending.

Ok, I created delete and append queries for each of them.

I also ensured that the following is set:
* Default Record Locking - No Locks
*Open databases by using record-level locking

I also checked the properties on the form and set to No Locks.

Same error message as before

 

[boblarson]

Ok, I created delete and append queries for each of them.

I also ensured that the following is set:
* Default Record Locking - No Locks
*Open databases by using record-level locking

I also checked the properties on the form and set to No Locks.

Same error message as before

Well, I think we're down to the upload of a copy. If you get to the point where you want to go that route, post back and I'll attempt to see what is going on.

 

[wailingrecluse]

Well, I think we're down to the upload of a copy. If you get to the point where you want to go that route, post back and I'll attempt to see what is going on.

Bob

I could supply you with the database without any data, which you could populate yourself with dummy figures.

Would that suit?

 

[boblarson]

Bob

I could supply you with the database without any data, which you could populate yourself with dummy figures.

Would that suit?

It would be much more helpful if you could provide some dummy data. I am willing to take a look but trying to populate a table with data I don't know anything about is going to take up a lot of my time and I'm volunteering my time to help you out but there are limits.

 

[wailingrecluse]

It would be much more helpful if you could provide some dummy data. I am willing to take a look but trying to populate a table with data I don't know anything about is going to take up a lot of my time and I'm volunteering my time to help you out but there are limits.

Bob

I have attached the DB.

Dummy data exists and I have removed some modules.

The code behind the form is still there, but you won't be able to run the append queries as they stand because they rely ultimately on everything coming from the linked table.

hopefully this will be enough?

 

[boblarson]

Okay, so see - that was very good. It allowed me to spot the problem right away. There are a couple of problems:

1. This form that you have the code on is bound to the table.

2. You are using a timer to run the queries but a few things about that:
a. You have the timer interval set to 600 (which is 600 milliseconds) and you
b. Never turn off the timer - so every 600 milliseconds this code is continuing to fire.

3. You can't close the form in the code because the code is on the form you are telling it to close. It can't close itself until all code has been run.

I think you need to have the form code open the frmReload and then close itself and put the code to run the queries on the frmReload and then have it reopen the other form and then close itself.

For the reload form you can set the status between queries but you also need to include this in between each:

' code for query here then
Me.Repaint
DoEvents
' code for next query here then
Me.Repaint
DoEvents


that way you can have it update in between each query to really show the status.