ACC: Database Grows Rapidly When You Modify SQL Statements
ID: Q165823
|
The information in this article applies to:
-
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:
Q124391
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.
- On the Query menu, point to SQL Specific, and then click Pass-Through.
- In the SQL Pass-Through Query window, type the following SQL statement:
sp_test
- Save the query as qryPassThruTest and close it.
- Create a module and type the following line in the Declarations section:
Option Explicit
- 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
- 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:
Q131534
ACC: How to Simulate Parameters in an SQL Pass-Through Query
Additional query words:
SPT Grow increase Bloat Pass-Thru Thru Insert Into Delete Union Update Append
Keywords : kbprg QryPass QryParm
Version : WINDOWS:2.0,97
Platform : WINDOWS
Issue type : kbprb