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
- Click OK when Microsoft Access prompts you that the records could not be added.
- Type the following in the Debug window, and then press ENTER:
?FileLen(CurrentDb.Name)
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000.
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
- Open the sample database Northwind.mdb.
- On the Tools menu, point to Database Utilities, and then click Compact Database.
- Press CTRL+G to open the Debug window.
- Type the following in the Debug window, and then press ENTER:
?FileLen(CurrentDb.Name)
- Create a new query based on the Order Details table.
- On the Query menu, click Append Query.
- Select the Order Details table from the list, and then click OK.
Add the following fields to the QBE grid:
Query: qryAppendToOrderDetails ------------------------------ Type: Append Query Field: Order Details.* Table: Order Details Sort: Append To: Order Details.*
- Save the query as qryAppendToOrderDetails and close it.
- In the Database window, select the qryAppendToOrderDetails query, and then click Open.
- Click Yes when Microsoft Access warns you that the append query will modify your data and asks if you want to proceed.
- Click Yes when Microsoft Access asks you if you want to append the rows.
- 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.
- Type the following line in the Debug window, and then press ENTER:
?FileLen(CurrentDb.Name)
- Start another instance of Microsoft Access and open the sample database Northwind.mdb.
- 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.
Additional query words: pra size
Keywords: kbbug KB174341