ACC97: Failed Append Query Causes Bloat in Multi-User Database
ID: Q174341
|
The information in this article applies to:
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)
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. 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)
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.
- 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)
Note that the file size of Northwind.mdb is still reported to be
1,542,144 bytes, or slightly larger.
- 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 : GnlMu QryAppnd RltRef
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug