Having trouble autoatically deleting tables

[choward]

Hi Guys,

i have set up a piece of code where after an update of a combo box, it will delete a table which is associated to a subform then rebuild the table based on the value selected on the combo box - then reload the form (basically it's a way of generating a query but in a table).

When i do all these steps by han, it works perfectly. BUT now i try and automate it, it crashes saying "Table is locked by another user". Any ideas? Here is the code (i have split it into subroutines in case that was the problem - ignore the sSleep command)

Private Sub comCAS_AfterUpdate()
Call close_CASEditor
sSleep 500
Call build_CASEditor_tables
sSleep 500
Call open_CASEditor
End Sub

Sub close_CASEditor()
DoCmd.OpenQuery "Update Current CAS"
DoCmd.Close acForm, "CAS Editor"
End Sub

Sub build_CASEditor_tables()
DoCmd.DeleteObject acTable, "CAS Editor A" ###### IT FAILS HERE!
DoCmd.DeleteObject acTable, "CAS Editor B"
DoCmd.OpenQuery "CAS Course Copy"
DoCmd.OpenQuery "CAS Course Demands Copy"
End Sub

Sub open_CASEditor()
DoCmd.OpenForm "CAS Editor"
End Sub

 

[choward]

Forgive my two earlier posts - my PC crashed and i thought they hadn't been posted.

 

[CyberLynx]

You must be still connected to the table in some fashion....somewhere. Does the SubForm Control itself have Linked (Master/Child) Fields to the Parent Form?

I believe you will need to disable all connection aspects of the Table from the SubForm and Parent Form if a link is present.

You should be able to get away with something like this:

- Clear the RecordSource property for the SubForm;
- Clear the Link MasterChild Fields properties For the SubForm Control;
- Delete the table;
- Reestablish the SubForm RecordSource to new Table
- Reestablish the SubForm Control Link Master/Child Field (if any);
- Requery the SubForm

.