ACC: Database Grows Rapidly When You Modify SQL Statements

• Microsoft Access versions 2.0, 97

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:

        ARTICLE-ID: Q124391
        TITLE     : 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.

1. Start Microsoft Access and create a new blank database. Note the size of the database, which should be approximately 64 KB or less.

2. Create a new query not based on any table in Design view.

3. On the Query menu, point to SQL Specific, and then click Pass-Through.

4. In the SQL Pass-Through Query window, type the following SQL statement:

         sp_test
   

5. Save the query as qryPassThruTest and close it.

6. Create a module and type the following line in the Declarations section:

         Option Explicit
   

7. 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
   
   

8. 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.

REFERENCES

For an example of modifying the SQL property of a pass-through query, see "Building Applications with Microsoft Access 97," Chapter 19, "Developing Client/Server Applications," pages 550-551.

For more information about using parameters in pass-through queries, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q131534
   TITLE     : ACC: How to Simulate Parameters in an SQL Pass-Through
               Query