General Discussion - Database Size and Actual Record remove

Print Page | Close Window

Database Size and Actual Record remove

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: General Discussion
Forum Description: General discussion and chat on any topic.
URL: https://forums.webwiz.net/forum_posts.asp?TID=10135
Printed Date: 31 March 2026 at 11:59am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Database Size and Actual Record remove

Posted By: meteor
Subject: Database Size and Actual Record remove
Date Posted: 25 April 2004 at 2:45am

Hello
at Quick start: Think of that i have a data base with 2000 record's and 5 MB size after adding about 2000 record's to Data base DB size Changed to about 10MB but When I removed about 3000 records from DataBase it's Size dosnt changed and remained at 10MB(Seems records Removed Logically) ok Now add 1000 record's to DB and No change on Size . I think that when remove records it fill records with Empty(or Delete Logically them).and after adding new records it Replace New Recors With Removed One. not physically remove records Because DataBase Size Dos Not Changed;how think about this and how can I Remove Records Physically(effect to DB Size that if you added record DB size Change to Up and when Remove Record DB Size Change To down)

10000 records -> 10MB
Add 10000 records -> 20MB
Remove 15000 Records -> 20MB
Add 2000 records -> 20MB
Add 20000 Records -> 30MB

Is Any Command in Access 97 To ZAP(?) Database (like FoxPro)

Sorry For bad ENGLISH

-------------
Sincerely
--------------------
http://www.TacPlusPlus.com - PowerFull Scripts For NTTacPlus

Replies:

Posted By: Mart
Date Posted: 25 April 2004 at 3:23am

I think you want compact & repair

Posted By: dj air
Date Posted: 25 April 2004 at 12:13pm

when using a database after deleteing records.. you have to do a compact & repair like mart said.

Posted By: meteor
Date Posted: 25 April 2004 at 3:23pm

tanx for answers but i search for a way in programing . is any command that do this for me ? is any command in recordSet or Connections for compacting?

-------------
Sincerely
--------------------
http://www.TacPlusPlus.com - PowerFull Scripts For NTTacPlus

Posted By: Mart
Date Posted: 25 April 2004 at 3:35pm

You don't need to do it programmatically. You can just download your database, open it in Access and click Tools > Database Utilities > Compact and repair Database. Then save your database and upload it over the original database.

Posted By: Mart
Date Posted: 25 April 2004 at 3:36pm

btw If it's your forum database that you want to compact and repair I think there is a option to do it automatically in the admin panel.

Posted By: dpyers
Date Posted: 25 April 2004 at 4:16pm

Many code examples

http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=asp+compact+repair+access - http://www.google.com/search?sourceid=navclient&ie=UTF-8 &oe=UTF-8&q=asp+compact+repair+access

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: meteor
Date Posted: 25 April 2004 at 11:54pm

tanx tanx tanx ......
the final way found the two I think this two code Should Work

http://www.dotnetindex.com/articles/59/?SESSION=607828084&REF=http%3A%2F%2Fwww%2Ehotscripts%2Ecom%2FDetailed%2F30553%2Ehtml - http://www.dotnetindex.com/articles/59/?SESSION=607828084&am p;REF=http%3A%2F%2Fwww%2Ehotscripts%2Ecom%2FDetailed%2F30553 %2Ehtml

<%
oldFile = Server.MapPath("db.mdb")
newFile = Server.MapPath("bak.mdb")

  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& oldFile
  strConnBak = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newFile

  Set objJRO = Server.CreateObject("JRO.JetEngine")
objJRO.CompactDatabase strConn, strConnBak
  Set objJRO = Nothing

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
 If objFSO.FileExists(newFile) And objFSO.FileExists(oldFile) Then
 objFSO.DeleteFile(oldFile)
 objFSO.MoveFile newFile, oldFile
 Response.Write "Compact and Repair was successful."
 Else
 Response.Write "Compact and Repair failed."
 End If

Set objFSO = Nothing
%>
------------------------------------------------------------ --

at :
http://dmxzone.com/ShowDetail.asp?NewsId=6398 - http://dmxzone.com/ShowDetail.asp?NewsId=6398

<% Option Explicit %>
<%
'Dimension variables
Dim objJetEngine 'Holds the jet database engine object
Dim objFSO 'Holds the FSO object
Dim strCompactDB 'Holds the destination of the compacted database
Dim strDbPathAndName
Dim strCon

strDbPathAndName = Server.MapPath("database.mdb")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDbPathAndName
%>
<html>
<head>
<title>Compact And Repair Access Database</title>
</head>
<body>
<h2>Compact And Repair Access Database</h2>
 
<%
'If this is a post back run the compact and repair
If Request.Form("postBack") Then %>
<table width="80%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<ol>
<%

'Create an intence of the FSO object
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

'Back up the database
objFSO.CopyFile strDbPathAndName, Replace(strDbPathAndName, ".mdb", "-backup.mdb", 1, -1, 1)

Response.Write(" <li>Database backed up to:- " & Replace(strDbPathAndName, ".mdb", "-backup.mdb", 1, -1, 1) & " </li>")

'Create an intence of the JET engine object
Set objJetEngine = Server.CreateObject("JRO.JetEngine")

'Get the destination and name of the compacted database
strCompactDB = Replace(strDbPathAndName, ".mdb", "-tmp.mdb", 1, -1, 1)

'Compact database
objJetEngine.CompactDatabase strCon, "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strCompactDB

'Display text that new compact db is created
Response.Write(" <li>New compacted database:- " & strCompactDB & " </li>")

'Release Jet object
Set objJetEngine = Nothing

'Delete old database
objFSO.DeleteFile strDbPathAndName

'Display text that that old db is deleted
Response.Write(" <li>Old uncompacted database deleted:- " & strDbPathAndName & " </li>")

'Rename temporary database to old name
objFSO.MoveFile strCompactDB, strDbPathAndName

'Display text that that old db is deleted
Response.Write(" <li>Rename compacted database from:- " & strCompactDB & " To:- " & strDbPathAndName & " </li>")

'Release FSO object
Set objFSO = Nothing

Response.Write(" The Access database is now compacted and repaired")

%></ol></td>
</tr>
</table>
<%
Else

%>
<p

-------------
Sincerely
--------------------
http://www.TacPlusPlus.com - PowerFull Scripts For NTTacPlus