How to quickly set free a table used by another process?

[inso18]

Hi Everyone.

I have a subform within a form. In the form there's a command that deletes fields from the table (tblTable) on which the subform is based.

The command deletes fields from tblTable by executing the sub:

Sub Delete1()
    DoCmd.RunSQL "ALTER TABLE tblTable DROP COLUMN Field1"
End Sub

The SQL code can only be executed when the table which it is executed upon isn't in use in the time of execution.

So I rewrote the sub and now it is:

Sub Delete1()
    'first I'm setting free Table1:
    Forms.frmMain.sfrmSubForm.SourceObject = ""
    DoEvents
    'then I execute the SQL code
    DoCmd.RunSQL "ALTER TABLE tblTable DROP COLUMN Field1"
End Sub

Still, I get the following error during the execution of the SQL:
"Run-time error '3211': The database engine could not lock 'tblTable' because it is already used by another person or process"

Somehow tblTable isn't being set free during the execution of the sub.

Running the sub again, after the SourceObject property is already "", the SQL code gets executed succesfully.

Is there a way to set free tblTable on the first run?


Thanks very much for your time,
inso18

 

[joelflorendo]

what if you try to refresh or requery the subform after you reset it's source object?

 

[inso18]

Requery doesn't help, refresh can't be done cause there are no underlying record source to refresh.


I thought there might be some special command or hack for unlocking the table immediately that that the more skilled Access users might know about.

 

[joelflorendo]

Odd, I ran your code and it worked for me. I got the error you specified one time but I can't seem to reproduce it anymore.

This also worked for me so maybe try it on your end:

Instead of setting

.SourceObject=""

set it equal to something that does exist and requery

.SourceObject="dummyForm"
.Requery

then run your sql to remove the field.

 

[inso18]

Nope, doesn't work for some reason.

I've tried also to do .Form.RecordSouce = "" but get the error "Operation Not Possible when in Transaction" or something of that kind

Only if I delete the subform from the form manually, or reset its RecordSource manually before performing the delete does everything work.

 

[inso18]

joelflorendo, maybe you can send me the code you ran?

thanks, inso18

 

[joelflorendo]

I pretty much copied your code verbatim except for the table, form, and field names where appropriate. It even works without a requery.

Private Sub Command2_Click()
    'first I'm setting free Table1:
    Forms.frmTest.frmSubTest.SourceObject = ""
    
    DoEvents
    'then I execute the SQL code
    DoCmd.RunSQL "ALTER TABLE tblTest DROP COLUMN RemFld"
End Sub

If I comment out the line that changes the sourceobject to blank, then I get the error. But otherwise it works for me.

What are you doing before this code fires? Do you possibly have another object open that references this table? Are you changing a record from this table right before you run the sql?

Sorry, I know these are obvious questions but better to rule them out now.

 

[inso18]

Thanks for your replay
Sorry I couldn't reply earlier.

My code is similiar, but before this code is executed On_Delete event of the main form which recordsource is not related to tblTest

Maybe that's why I'm having the problem?
Is there any way to do it with On_Delete event?

inso18

 

[inso18]

*
My code is similiar, but it is executed On_Delete event of the main form which recordsource is not related to tblTest.

Maybe the problem arises from the code being executed On_Delete event (even though the form from which On_Delete is executed is not related to tblTest)?

Is there any way to preform this from On_Delete event anyway?

 

[joelflorendo]

Hmmm...odd. I set up my form with a recordsource unrelated to the subform data and the code is fired on the On Delete Event. It successfully removed the field from the table the subform is based on using the same code. There must be something else that is locking the table...