Can I compact / repair an Access database from ASP code?

Yes, you can compact and repair an Access database using ASP. Thanks to Craig Starnes for suggesting this article. 
 
I created a simple table, with an Autonumber column and a text(50) column. I created a macro that would insert rows with random characters into the text columns. I set the repeat on the macro to 30,000 and ran it. While I was waiting for Access to struggle through the task and release my CPU so I could do other things on the box, I went and brushed my teeth. When I came back, it wasn't finished yet. So I read Tolkien's trilogy. When it finally completed the task, I observed the size of the MDB file, and it was around 1.3 MB. I then wrote a query to delete roughly half the records, interspersed (e.g. I did not delete the first or last 15,000 rows - I wanted to see what compacting would do to a more realistically adjusted Autonumber column). I closed the database, and lo and behold, the file was still 1.3 MB (even though I had just cut the actual storage size in half). 
 
So, I decided to try out Craig's code, which came to me something like this: 
 

<%       oldDB = Server.MapPath("/accessTest.mdb")      bakDB = Server.MapPath("/accessTestBack.mdb")      newDB = Server.MapPath("/accessCompact.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   %>

 
Sure enough, this code reduced the size of my MDB file by more than half (!) and did it rather quickly. I'm not sure what would have happened to requests for the database that came in at exactly the same time, but if you're using Access in a production environment, this is probably not a high priority anyhow. 
 
Note that compacting the database also performs the 'repair' operation you might otherwise perform only through Access' GUI. 
 
For more ways to compact an Access database, see the following articles: 
 
    Online compacting of Access databases (ASPAlliance.com) 
 
    Compact and Repair (ASPerium.com) 
 
    Compact (compress) mdb database (pstruh.cz) 
 
Note that in Access 97 / 2000, compacting a database would reset any tables with AUTONUMBER columns (if all rows have been deleted, the next AUTONUMBER resets to 1; otherwise, it will be the next number available). With Access XP, this is no longer the case (see KB #287756). 
 
If you are looking for information on how to synchronize multiple connections to a single database using ASP and JRO, please see KB #200300. The example there was written in VB, but you could easily port it to ASP.