Form has my table locked down tight even after docmd.close

Question

Sorry for the wall of text guys but this one requires expliaining, way too much code to post...

I'm importing fixed width files into access in methods that require data entry. I import the file using transferText into two specs (ones global, the other is special circumstance).

I have a function that uses DAO to cycle through all Field objects in TableDefs to build a duplicate table including an AutoIncrement PK so I have the ability to edit these records. I push the data into that table with INSERT INTO.

Works great. Errors are found, user goes into data entry to manually correct them which beats sifting through 400 character lines and reorganizing everything the way its supposed to be. Works great!

The problem: When the data entry changes are made a commit button is pressed which calls a function inside a module outside of the form. It closes the data entry form and pushes the information back to the original table minus the autoincremented PK, and is SUPPOSED to DROP the replicated table with ID's, and generate a new one searching once again for errors...

It pushes back to the original just fine, but it will not DROP the ID table. Always returns to me with a message indicating this table is locked. Ive noticed the table is indefiniatly locked down until all functions/subs exit. At any time stepping through the code I cannot delete it manually, once the execution has finished I am able to remove it.

I am assuming that since I called this through a command in the form, that the lock will not be released until all code finishes and the form terminate can be called and do its thing. Any thoughts? Yes this is very barbaric but it works quite well, I just need to be able to rip this other table off the planet so I can redrop an updated copy...

In the worst case I can make the user close the form out and hit another button in the main form but this is being designed heavily with user compitence in mind. However this now has my full attention and would like to at least find a solution even if it's not the optimal one.

-EDIT-

Two forms are used in this problem

FormA (Role: Load in and search for problems)

Examine button is pressed that:

 - Uses TextTransfer based on predefined specs into tempExtract to
       import the file

 - DAO fires off on the Fields collection in tableDefs for
   tempExtract, creates new table tempExtractID

 - Performs searches through the file to find errors.  Errors are saved to
   a table Problem_t.  Table contains Problem_ID (Set from the ID field
   added to tempExtractID) and Description

 - Execution of these tasks is successfully requerying the initial
   form to showing a list of problems and number of occurances.  A button
   gains visibility, with onClick that opens the form DataEntry.            

 - At this point in the code after DAO execution, I can DROP the table
   tempExtractID.  DAO is NOT used again and was only used to build a new table.

FormB - Data Entry Form

As soon as I open this form, the table tempExtractID becomes locked and I cannot drop the table. The recordsource to the form querys tempExtractID against the ID's in Problems_t to return only what we need to key.

I cannot drop the table until the form has fully terminated. Button on the Data Entry form is pressed to commit changes, in which there are only 5 lines of code that get to fire off before I get my lock error.

*Xargs refers to the list of Field names pulled earlier through DAO.  As DAO loops through Field objects, the physical names are added to an Xargs String which is placed in this table.  Basically everything but the AutoNumber is being inserted back

    docmd.Close acForm, "frmDataEntry", acSaveNo
    call reInitializeExtract
         > docmd.RunSQL "DELETE FROM tempExtract"
         > docmd.RunSQL "INSERT INTO tempExtract SELECT (" & DLookup("Value", "CONFIG_t", "Item = 'Xargs'" & ") FROM tempExtractID"
    docmd.DeleteObject acTable, "tempExtractID"

This is the only code that is run between the time where the form is opened (Where the table first gets locked) and continues to be locked until all subs & functions have completed.

Answer 1

I suggest setting the recordsource of the form to vbNullString and then deleting the table. This should work, unless you also have comboboxes and so forth bound to this table.