If you delete data or objects in an Access database, or delete objects in an Access project, the Access database or Access project may become fragmented and use disk space less efficiently. Compressing an Access database or Access project actually copies the file and reorganizes how the file is stored on disk. Compression optimizes the performance of both Access databases and Access projects.
So when we found the ASP program unbearably slow due to the ever-increasing database, we thought of compressing it. But the conventional approach is to download it locally and then use MSaccess to complete the compression operation, and then upload it!
It should be noted that this program is actually connected to the JET engine through FSO permissions, so before using it, please confirm that your server supports FSO (filesystem object) permissions and install the latest ACCESS driver! For safety reasons, please back up the original database before compressing! Running environments that have passed the test:
WIN98SE+PWS, WIN2000+IIS5.0
The following is the source code I compiled. Copy and save it as compact.asp and upload it to the directory where the database is located for normal use.
<html>
<head>
<title>ACCESS database compression program</title>
</head>
<body bgcolor="e0f8ef">
<div>
<div align="center"><font color="#3300FF">
<b><font size="5">Universal ACCESS database online compression program</font></b></font><br>
</div>
<div><br>
This program is actually connected to the JET engine through FSO permissions.
Therefore, please confirm that your server supports FSO before using it.
(filesystemobject) permissions and install the latest ACCESS driver!
For safety reasons, please back up the original database before compressing! </div><br>
<div align="center">Operating environment: WIN98SE+PWS, WIN2000+IIS5.0 <br>
<%
Const JET_3X = 4
Function CompactDB(dbPath, boolIs97)
Dim fso, Engine, strDBPath
strDBPath = left(dbPath,instrrev(DBPath,""))
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(dbPath) Then
Set Engine = CreateObject("JRO.JetEngine")
If boolIs97 = "True" Then
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath, _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & "temp.mdb;" _
& "Jet OLEDB:Engine Type=" & JET_3X
Else
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath, _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & "temp.mdb"
End If
fso.CopyFile strDBPath & "temp.mdb",dbpath
fso.DeleteFile(strDBPath & "temp.mdb")
Set fso = nothing
Set Engine = nothing
CompactDB = "Your database, " & dbpath & ", has been compressed" & vbCrLf
Else
CompactDB = "The database path or name you entered was not found, please try again" & vbCrLf
End If
End Function
%>
</div>
</div>
<form name="compact" method="post" action="compact.asp">
<div align="center">
<font size="2"><b><font color="#FF0000">
Compression options, please fill in carefully! </font></b><br>
<br>
Enter the full name of the database:
<input type="text" name="dbpath">
(Includes extensions such as MDB, ASA, ASP, etc.)<br>
<br>
<input type="checkbox" name="boolIs97" value="True">
Check if it is an ACCESS97 database<br>
(Default is ACCESS2000 database)<br>
<br>
<input type="submit" name="submit" value="Confirm compression">
</font></div>
</form>
<div align="center"><font size="2">
<%
Dim dbpath,boolIs97
dbpath = request("dbpath")
boolIs97 = request("boolIs97")
If dbpath <> "" Then
dbpath = server.mappath(dbpath)
response.write(CompactDB(dbpath,boolIs97))
End If
%>
<br>
</font></div></body></html>