VB icon

Compact and Repair Access Database

Email
Submitted on: 1/6/2015 3:24:00 AM
By: Chris Peneguy (from psc cd)  
Level: Advanced
User Rating: By 6 Users
Compatibility: ASP (Active Server Pages)
Views: 1698
 
     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.
 
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
'**************************************
' for :Compact and Repair Access Database
'**************************************
Use as you wish
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: Compact and Repair Access Database
' Description: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.
 
' By: Chris Peneguy (from psc cd)
'**************************************

<% 
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:\InetPub\wwwroot\_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 ("<Select Name=""DBFileName"">")
For Each Item In oFolders.GetFolder(strFolder).Files
If LCase(Right(Item, 4)) = ".mdb" Then
Response.Write ("<Option Value=""" & Replace(Item, strFolder, "") _
& """>" & Replace(Item, strFolder, "") & "</Option>")
End If
Next
Response.Write ("</Select>")
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
%>
<p><font size="4">Compact and repair database</font></p>
<form method="POST" action="">
<p><%dbList%><input type="submit" value="Compact" name="cmd"></p>
</form>


Other 1 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Advanced category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.