Access Database size

[Nitin]

I am using access database for my application, whenever I insert records the
size of the data base increases, But when I delete the records the database
size does not decrease. What is the problem???
ThanX
Nitin

 

[Eric Sabine]

Access does not self-shrink. You can do it manually, i.e., compact &
repair, or do it from code from another application. Please look in the
access newsgroups for assistance.

hth
Eric

 

[Ken Tucker [MVP]]

Hi,

Compact and repait the database. Add a reference to the microsoft
jet and replication objects in the com tab.



Dim jro As New JRO.JetEngine

Dim fl As File

Dim strSource As String

Dim strConn As String

Dim strDest As String

Dim strDay As String

Dim drDest As Directory

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn += "Data Source = "

strSource = Application.StartupPath

strDest = strSource

strSource += "\test.mdb"

strDest += "\test.bak"

If fl.Exists(strDest) Then

fl.Delete(strDest)

End If

Try

jro.CompactDatabase(strConn + strSource, strConn + strDest)

fl.Copy(strDest, strSource, True)

Catch

fl.Copy(strSource, strDest, True)

Finally

fl.Copy(strDest, strSource, True)

jro = Nothing

End Try


Ken
--------------------------------
I am using access database for my application, whenever I insert records the
size of the data base increases, But when I delete the records the database
size does not decrease. What is the problem???
ThanX
Nitin

 

[Derrick [MCSD]]

Hi Nitin,

The posts by Eric and Ken are right, repair and compact is the answer to the
"what do I do" portion of your question. As to the "why does this happen"
part, most database packages do not do a physical delete when a "DELETE" SQL
statement comes in. Rather, the records to be deleted are marked with a
"tombstone" that indicates that they are logically deleted. To physically
remove them, a "shrink" operation needs to be invoked. In the case of
Microsoft Access, that is taken care of by the Repair (reindex) and Compact
(physically remove all records marked for deletion).

HTH,
Derrick