ACC97: Failed Append Query Causes Bloat in Multi-User Database (174341)
The information in this article applies to:
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:
- 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:
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.
REFERENCES
For more information about the Execute method, search the Help Index for
"Execute method," or ask the Microsoft Access 97 Office Assistant.
Modification Type: | Major | Last Reviewed: | 9/25/2003 |
---|
Keywords: | kbbug KB174341 |
---|
|