ACC2000: Manipulating Objects with DAO May Cause Database Bloat
ID: Q197953
|
The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.
SYMPTOMS
When you use Data Access Objects (DAO) to create objects in a database,
the size of the database increases substantially during the operation.
After compacting, the size of the database is much smaller.
Microsoft provides programming examples for illustration only, without warranty
either expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes that you
are familiar with the programming language being demonstrated and the tools used to
create and debug procedures. Microsoft support professionals can help explain the functionality
of a particular procedure, but they will not modify these examples to provide added
functionality or construct procedures to meet your specific needs. If you have limited
programming experience, you may want to contact a Microsoft Certified Solution Provider
or the Microsoft fee-based consulting line at (800) 936-5200. For more information about
Microsoft Certified Solution Providers, please see the following page on the World Wide Web:
http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the
following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
RESOLUTION
Use SQL Data Definition Language (DDL) statements rather than DAO to
create or modify database objects. For example, you can use the following
procedure to work around the behavior demonstrated in the "Steps to
Reproduce Problem" section later in this article:
Sub CreateTables()
Dim db As Database
Dim sql As String
Dim i As Integer, j As Integer
Set db = CurrentDb()
For i = 1 To 20
sql = "CREATE TABLE Table" & i & " ("
For j = 1 To 200
sql = sql & "Field" & j & " TEXT,"
Next
sql = Left$(sql, Len(sql) - 1) & ");"
db.Execute sql
Next
Application.RefreshDatabaseWindow
End Sub
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed
at the beginning of this article.
MORE INFORMATION
This behavior typically occurs when you use DAO to create or modify a large
number of database objects. The following example demonstrates this by
using DAO to create twenty tables, each with two hundred fields. In this
example, reducing the number of fields created in the example greatly
reduces the amount of database bloat.
Steps to Reproduce Problem
- Open the sample database Northwind.mdb.
- On the Tools menu, point to Database Utilities, and then click Compact
And Repair Database.
- Press CTRL+G to open the Immediate window.
- Type the following in the Immediate window, and then press ENTER:
?FileLen(CurrentDb.Name)
This function returns the file size in bytes of the currently
opened database (Northwind.mdb). Note of the current size.
- 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 CreateTables()
Dim db As Database
Dim t As TableDef
Dim f As Field
Dim i As Integer, j As Integer
Set db = CurrentDb()
For i = 1 To 20
Set t = db.CreateTableDef("Table" & i)
For j = 1 To 200
Set f = t.CreateField("Field" & j)
f.Type = dbText
f.size = 50
t.Fields.Append f
Next
db.TableDefs.Append t
Next
Application.RefreshDatabaseWindow
End Sub
- On the Debug menu, click Compile Northwind. When Microsoft Access
prompts you for the name of the module, click OK to accept the
default name.
- To run this procedure, type the following line in the Immediate
window, and then press ENTER. It may take several minutes for this
procedure to run.
CreateTables
Note that twenty tables are added to the database, each with two
hundred fields.
- Type the following line in the Immediate window, and then press ENTER:
?FileLen(CurrentDb.Name)
Note that the file size of Northwind.mdb is now reported to be more than 52 megabytes.
- On the Task Bar click Microsoft Access to get back to the Database
window.
- On the Tools menu, point to Database Utilities, and then click Compact
and Repair Database.
- Press CTRL+G to open the Immediate window.
- Type the following in the Immediate window, and then press ENTER:
?FileLen(CurrentDb.Name)
Note that the file size of Northwind.mdb after compacting is now back
to the size you saw in step 4.
REFERENCES
For more information about data-definition queries, click Microsoft Access Help on the
Help menu, type "what is an SQL query" in the Office Assistant or the Answer Wizard,
and then click Search to view the topic.
Additional query words:
pra
Keywords : kbdta MdlDao
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug