Compact and Repair Access Database

---

Compact and Repair
You can use the "Compact and Repair" function in Access from ASP code. The following code is an example of how this can be done. Note that when you decide to "Compact and Repair" your Access database, some autonumbers can be changed. Access makes all autonumbers consecutive.
This code uses one database, but I'm sure the code can easily be changed so that the listbox displays, for example, all the databases in one folder.

Original Author: Chris Peneguy

API Declarations

Use as you wish

Code

<%
Const Jet_Conn_Partial = "Provider=Microsoft.Jet.OLEDB.4.0; Data source="
Dim strDatabase, strFolder, strFileName
'#################################################
'# Edit the following two lines
'# Define the full path to where your database is
strFolder = "F:InetPubwwwroot\_db"
'# Enter the name of the database
strDatabase = "YourAccessDatabase.mdb"
'# Stop editing here
'##################################################
Private Sub dbCompact(strDBFileName)
Dim SourceConn
Dim DestConn
Dim oJetEngine
Dim oFSO
SourceConn = Jet_Conn_Partial & strFolder & strDatabase
DestConn = Jet_Conn_Partial & strFolder & "Temp" & strDatabase
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
Set oJetEngine = Server.CreateObject("JRO.JetEngine")
With oFSO
    If Not .FileExists(strFolder & strDatabase) Then
      Response.Write ("Not Found: " & strFolder & strDatabase)
      Stop
    Else
         If .FileExists(strFolder & "Temp" & strDatabase) Then
            Response.Write ("Something went wrong last time " _
            & "Deleting old database... Please try again")
           .DeleteFile (strFolder & "Temp" & strDatabase)
         End If
   End If
End With
With oJetEngine
.CompactDatabase SourceConn, DestConn
End With
oFSO.DeleteFile strFolder & strDatabase
oFSO.MoveFile strFolder & "Temp" _
& strDatabase, strFolder& strDatabase
Set oFSO = Nothing
Set oJetEngine = Nothing
End Sub
Private Sub dbList()
Dim oFolders
Set oFolders = Server.CreateObject("Scripting.FileSystemObject")
  Response.Write ("")
  For Each Item In oFolders.GetFolder(strFolder).Files
  If LCase(Right(Item, 4)) = ".mdb" Then
    Response.Write ("    & """>" & Replace(Item, strFolder, "") & "")
  End If
Next
Response.Write ("")
Set oFolders = Nothing
End Sub

%>
<%
' Compact database and tell the user the database is optimized
Select Case Request.form("cmd")
Case "Compact"
dbCompact Request.form("DBFileName")
Response.Write ("Database " & Request.form("DBFileName") & " is optimized.")
End Select
%>


Compact and repair database

<%dbList%>

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 97 times

Categories

ASP/ HTML

Attachments

No attachments for this post

Comments

No comments have been added for this post.

You must be logged in to make a comment.