ACC1x: Form Handle Not Released When Closed with Access Basic

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

After closing a form (using the Close macro action) that was opened through Access Basic code, the file handle for the table that the form is based on is not released. Because the file handle is not released, no functions that require exclusive use on that table can be executed (for example, deleting the table). When a function that needs exclusive use of that table is executed, the following error will occur:

Couldn't lock table '<tablename>'; currently in use.

CAUSE

You are not guaranteed that the user interface (UI) will be synchronized with the underlying Microsoft Access code in all cases. In this case, the record locking is done behind the scenes. Microsoft Access releases the table when it hits its idle loop (that is, the UI optimization).

RESOLUTION

After the form is opened in Access Basic code, insert a DoEvents command before the function that needs exclusive use of the table, to cause Microsoft Access to yield execution to the operating system, so that it can process events. When the DoEvents command occurs it allows Microsoft Access to process the pending unlock sequence.

STATUS

This behavior no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

The following steps provide example code that reproduces this error. There is a comment in the Access Basic code identifying where to insert a DoEvents command to resolve this error.

Steps to Reproduce Problem

1. Open the sample database NWIND.MDB. Create a module and enter the following code:
   

   
         '*************************************************************
         'Declarations section of the module.
         '*************************************************************
   
         Option Explicit
   
         '================================================================
         ' Create and save the following TempCust() function in the Module
         '================================================================
         Function TempCust()
            DoCmd SelectObject a_table, "Customers", True
            DoCmd CopyObject , "Cust2"
            DoCmd OpenForm "Customers 2", a_normal, "", "", a_edit, a_dialog
            '**** This is where the DoEvents should be inserted to fix ****
            DoCmd SelectObject a_table, "Cust2", True
            DoCmd DoMenuItem 1, 1, 4
         End Function 




2. Open the Customers form in Design view, and change the RecordSource property to Cust2.



3. Create a command button on this form with the following properties:
   

   
         Object: Command Button
         -----------------------
         ControlName: Close Form
            Caption: Close Form
            OnPush: ExitForm 




4. Save the modified Customers form as Customers 2.



5. Create and save the following macro:
   

   
         Macro Name   Action
         -------------------
         ExitForm     Close 




6. Create and save the following macro:
   

   
         Macro Name     Action     Argument
         ------------------------------------
         OpenCustForm   RunCode    TempCust() 




7. From the Database window, run the OpenCustForm macro that you just created. The Customers 2 form will open.



8. Click the Close Form macro button on this form. You will receive a dialog box that asks:
   

   
         Delete Table 'Cust2'? 




9. Choose OK. You will receive the following error message:
   

   Couldn't lock table 'Cust2'; currently in use.

Keywords : kbprg PgmErr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbprb