Microsoft KB Archive/174341

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

Article ID: 174341

Article Last Modified on 1/20/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q174341



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



SYMPTOMS
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:  Repeat steps 1 through 9 of the "Steps to Reproduce Problem" section later in this article. Start another instance of Microsoft Access and open the sample database Northwind.mdb. Switch back to the original instance of Microsoft Access.  Create a module and type the following line in the Declarations section if it is not already there: Option Explicit   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  To test this procedure, type the following line in the Debug window, and then press ENTER:

AppendDuplicateRecords

</li> Click OK when Microsoft Access prompts you that the records could not be added.</li> 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.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000.

<div class="moreinformation_section">

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
<ol> Open the sample database Northwind.mdb.</li> On the Tools menu, point to Database Utilities, and then click Compact Database.</li> Press CTRL+G to open the Debug window.</li> 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.</li> Create a new query based on the Order Details table.</li> On the Query menu, click Append Query.</li> Select the Order Details table from the list, and then click OK.</li>  Add the following fields to the QBE grid: <pre class="fixed_text">       Query: qryAppendToOrderDetails --       Type: Append Query

Field: Order Details.* Table: Order Details Sort: Append To: Order Details.* </li> Save the query as qryAppendToOrderDetails and close it.</li> In the Database window, select the qryAppendToOrderDetails query, and then click Open.</li> Click Yes when Microsoft Access warns you that the append query will modify your data and asks if you want to proceed.</li> Click Yes when Microsoft Access asks you if you want to append the rows.</li> 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.</li> <li>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.</li> <li>Start another instance of Microsoft Access and open the sample database Northwind.mdb.</li> <li>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.</li></ol>

<div class="references_section">