Microsoft KB Archive/165823

= ACC: Database Grows Rapidly When You Modify SQL Statements =

Article ID: 165823

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q165823



SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

Each time you modify the SQL property of a query, the size of your database increases. This applies to SQL statements that you use in the RecordSource property of a form or report, as well as to stored queries on the Queries tab of the Database window.

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 your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



CAUSE
The query is saved each time you modify its SQL property. Because the Microsoft Jet database engine does not reuse the same space in the database when the query is saved, the database increases in size.



RESOLUTION
You can use the following techniques to work around the problem:  Compact your database, which can reduce the size of the database by compressing unused space in the database file. If you are modifying SQL pass-through queries, consider using temporary QueryDef objects in code when you need to alter the SQL statement. Because the query is not stored, the database does not increase in size. For more information about creating temporary queries, please see the following article in the Microsoft Knowledge Base:

124391 ACC: Sample Code for Running Temporary SQL Pass-Through Query

NOTE: A disadvantage to this method is that you cannot bind a form or report to a temporary QueryDef object. In Microsoft Access 97, use ODBCDirect to query an ODBC data source. For more information about opening a recordset using ODBCDirect, search the Help Index for OpenRecordset method or ODBCDirect workspaces.

NOTE: A disadvantage to this method is that you cannot bind a form or report to a recordset unless you programmatically bind each field and manage record navigation in code.



MORE INFORMATION
In general, your database grows in size as you work on the design of its objects. For example, repeated modifications to the design of a table, form, or report cause a database to grow; compacting the database restores it to a smaller size. However the growth is faster and more dramatic when you change the SQL property of a query, or if you change the SQL statement in the RecordSource property of a form or report. This is because the Jet database engine generates a new query plan each time you change the SQL, and the new plan is saved to an unused portion of your hard disk.

This behavior is reported frequently in databases that use SQL pass-through queries because application developers will often modify the SQL property of a query in code to change a parameter passed to a SQL Server stored procedure, for example, or to change an Insert, Update, or Delete statement.

Steps to Reproduce Behavior
The following example causes a database file to grow from 64 kilobytes (KB) to approximately 5564 KB. The example edits the SQL property of an SQL pass-through query 3000 times.  Start Microsoft Access and create a new blank database. Note the size of the database, which should be approximately 64 KB or less. Create a new query not based on any table in Design view.</li> On the Query menu, point to SQL Specific, and then click Pass-Through.</li> In the SQL Pass-Through Query window, type the following SQL statement:

sp_test

</li> Save the query as qryPassThruTest and close it.</li>  Create a module and type the following line in the Declarations section: Option Explicit </li>  Type the following procedure: Sub TestQDF Dim i As Long Dim qdf As QueryDef Dim db As Database

Set db = CurrentDb Set qdf = db.QueryDefs("qryPassThruTest")

For i = 1 To 3000 qdf.SQL = "sp_test " & Chr(34) & i & Chr(34) Next End Sub </li> To run the TestQDF procedure, type the following line in the Debug window (or the Immediate window in version 2.0), and then press ENTER.

TestQDF

When the procedure finishes, check the size of the database again. It will be approximately 5.5MB in size.</li></ol>

<div class="references_section">