Full Version: ...table already in use by another person or process.
UtterAccess Forums > Microsoft® Access > Access Forms
I have run into this many times, and always have to spend a lot of time to work around it.
Sometimes when I am using a listbox control, I have a need to delete the table that holds the underlying data and then rebuild the table. (Usually I delete just the records and append new ones. But sometimes I need to whack the whole table and build a new one with the same name).
When I do this, if my form is still open, I get an error that the table is in use and cannot be deleted.
Fair enough.
But why doesn't it work if I first set the control's source to nothing (freeing up the table?), delete the table, rebuild the table, reset listbox source?
Private Sub Rebuild_Table
me.ListBox.RowSource = ""
me.listbox.RowSource = "select * from tbl_MyTable"
I just tried doing something similiar:

    Me.List3.RowSource = ""
nbsp;   Dim i As Integer
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "t1"
    DoCmd.RunSQL "SELECT 1 as f1 INTO t1"
    For i = 1 To 10
        DoCmd.RunSQL "INSERT INTO t1 (f1) values(" & i & ")"
    Next i
    Me.List3.RowSource = "select * from t1"
    DoCmd.SetWarnings True

...and it seemed to work fine. Might the problem be in sRemoveTable?
Thanks lematt...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.