Compact and Repair Access database issues? 1

[dodge20]

I was thinking of adding a compact and repair function to my database control panel. Are there any problems with using this? Will the locking feature of an access database cause any problems? Any comments are appreciated.

Here is the script

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%  
    oldDB = Server.MapPath("../db/database.mdb") 
    bakDB = Server.MapPath("../db/databaseBack.mdb") 
    newDB = Server.MapPath("../db/database.mdb") 
 
    Set FSO = CreateObject("Scripting.FileSystemObject") 
 
    ' back up database 
 
    FSO.CopyFile oldDB, bakDB, true 
 
    ' compact database 
 
    Set Engine = CreateObject("JRO.JetEngine") 
    prov = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
    Engine.CompactDatabase prov & OldDB, prov & newDB 
    set Engine = nothing 
 
    ' delete original database 
 
    FSO.DeleteFile oldDB 
 
    ' move / rename our new, improved, compacted database 
 
    FSO.MoveFile newDB, oldDB 
    set FSO = nothing  
%>

Dodge20

 

[Sheco]

Maybe it would be better to wait until nobody was using the web app?

 

[QatQat]

Does that script work? If it does I learnt a new thing today.


Thanks

QatQat

Life is what happens when you are making other plans.

 

[dodge20]

Yes is does work, except the newDB needs to have a different name than the old db. It gets renamed back to the original. If the oldDB and newDB are named the same, it will error after it makes the backup.

So this would be better

<%  
    oldDB = Server.MapPath("../db/database.mdb")
    bakDB = Server.MapPath("../db/databaseBack.mdb")
    newDB = Server.MapPath("../db/databasecompact.mdb")
 
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
    ' back up database
 
    FSO.CopyFile oldDB, bakDB, true
 
    ' compact database
 
    Set Engine = CreateObject("JRO.JetEngine")
    prov = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    Engine.CompactDatabase prov & OldDB, prov & newDB
    set Engine = nothing
 
    ' delete original database
 
    FSO.DeleteFile oldDB
 
    ' move / rename our new, improved, compacted database
 
    FSO.MoveFile newDB, oldDB
    set FSO = nothing  
%>

Dodge20

 

[BrianB]

My experience is that if the db is in use, the compact routine will fail. You will need to handle that error. Another possibility would be to first have your db connector logic check to ensure that there is no "newDB" present before attempting a connection to the old. If the newDB exists, then we are either in the middle of a compact or just finishing it. That said, you are going to have to ensure that you never get a situation where the newDB exists, yet the compact rountine isn't running, lest your clients be left hanging waiting for the db to become available.

Brian Begy
Chicago Data Solutions

http://www.chicagodatasolutions.com

Beta testers needed for a .NET bug tracking application

http://www.itcollaborate.com

 

[dodge20]

Thanks that is what i was looking for.

Dodge20