ACC97: Failed Append Query Causes Bloat in Multi-User Database

• Microsoft Access 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Running an append query that fails causes the size of your database to increase substantially. After compacting, the size of the database is much smaller.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

CAUSE

The database is opened by multiple users, and the append query tried to duplicate values in a unique index.

RESOLUTION

Open the database exclusively before running the append query.

-or-

Run the query using the Execute method with the dbFailOnError option in a Visual Basic for Applications procedure. To use the Execute method in a Visual Basic for Applications procedure, follow these steps:

1. Repeat steps 1 - 9 of the "Steps to Reproduce Problem" section later in this article.

2. Start another instance of Microsoft Access and open the sample database Northwind.mdb.

3. Switch back to the original instance of Microsoft Access.

4. Create a module and type the following line in the Declarations section if it is not already there:

         Option Explicit
   

5. Type the following procedure:

         Sub AppendDuplicateRecords()
            Dim db As Database
            Dim qd As QueryDef
   
            On Error GoTo AppendDuplicateRecords_Err
            Set db = CurrentDb()
            Set qd = db.QueryDefs!qryAppendToOrderDetails
            qd.Execute dbFailOnError
   
         AppendDuplicateRecords_Exit:
            Exit Sub
   
         AppendDuplicateRecords_Err:
            MsgBox Error$
            Resume AppendDuplicateRecords_Exit
         End Sub
   
   

6. To test this procedure, type the following line in the Debug window, and then press ENTER.

         AppendDuplicateRecords
   

7. Click OK when Microsoft Access prompts you that the records could not be added.

8. Type the following in the Debug window, and then press ENTER:

         ?FileLen(CurrentDb.Name)
   

   Note that the file size of Northwind.mdb is reported to be approximately 1,554,432 bytes.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

All action queries (except append queries) write data to a temporary database stored on your hard disk if the query is run inside a transaction (default). When the transaction is finished, the data is purged from the temporary database into your database. However, append queries write directly to your database instead of to a temporary database.

You can use the Microsoft data access objects Execute method to work around the problem because it differs from the way Microsoft Access requests the database engine to execute the query. The method Microsoft Access uses forces the database engine to allocate additional space to the database to accommodate the records that could not be added when the append query failed. In single-user databases, this problem does not occur because the database engine is able to recycle the empty data pages currently allocated and does not need to allocate additional space. However, in multi-user databases, the database engine is not able to recycle empty data pages, and allocates additional space to the database to compensate.

Steps to Reproduce Problem

  1. Open the sample database Northwind.mdb.

  2. On the Tools menu, point to Database Utilities, and then click Compact
     Database.

  3. Press CTRL+G to open the Debug window.

  4. Type the following in the Debug window, and then press ENTER:

        ?FileLen(CurrentDb.Name)

     This function returns the file size in bytes of the currently
     opened database (Northwind.mdb). The file size of an unmodified copy
     of Northwind.mdb is approximately 1,542,144 bytes.

  5. Create a new query based on the Order Details table.

  6. On the Query menu, click Append Query.

  7. Select the Order Details table from the list, and then click OK.

  8. Add the following fields to the QBE grid:

        Query: qryAppendToOrderDetails
        ------------------------------
        Type: Append Query

        Field: Order Details.*
           Table: Order Details
           Sort:
           Append To: Order Details.*

  9. Save the query as qryAppendToOrderDetails, and close it.

 10. In the Database window, select the qryAppendToOrderDetails query, and
     then click Open.

 11. Click Yes when Microsoft Access warns you that the append query will
     modify your data and asks if you want to proceed.

 12. Click Yes when Microsoft Access asks you if you want to append the
     rows.

 13. Click No when Microsoft Access warns you that the append query can't
     add all the rows, and asks if you want to run the action query anyway.

 14. Type the following line in the Debug window, and then press ENTER:

        ?FileLen(CurrentDb.Name)

     Note that the file size of Northwind.mdb is still reported to be
     1,542,144 bytes, or slightly larger.

 15. Start another instance of Microsoft Access and open the sample
     database Northwind.mdb.

 16. Switch back to the original instance of Microsoft Access, and repeat
     steps 10 - 14.

     Note that the file size of Northwind.mdb is now reported to be
     33,490,944 bytes.

REFERENCES

For more information about the Execute method, search the Help Index for "Execute method," or ask the Microsoft Access 97 Office Assistant.