By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,556 Members | 1,572 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,556 IT Pros & Developers. It's quick & easy.

Access Database size

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nitin wrote:
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

Nov 21 '05 #2

P: n/a
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
--------------------------------
"Nitin" <lo*******@yahoo.com> wrote in message
news:uZ**************@TK2MSFTNGP15.phx.gbl...
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

Nov 21 '05 #3

P: n/a
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

"Nitin" <lo*******@yahoo.com> wrote in message
news:uZ**************@TK2MSFTNGP15.phx.gbl...
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

Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.