Forum Moderators: open
That said, here's an issue a lot of Access users run into, and here is the fix. If you do a lot of DELETING from your database, you may notice that the SIZE of the database doesn't tend to go down. Much like SQL, Access files will expand to the size they need to contain the data, but won't automatically decrease if that space is no longer needed. In order to shrink an Access database, you have to open it up in MS Access and click the "Compact and Repair Database" option. to shrink it down to the appropriate size for the data you're storing.
Anyway - here is a bit of ASP code that will help. This will make it so you don't have to download your database and shrink it if your DB was created in Access 2000 (Access version 9) and I'm sure you can find a similar bit of code for later versions. This will perform the "compact and repair database" on the fly from the server using The Access object and FileSystemObject. These should both already be on the web server you're using:
===================
Function CompactDatabase()
Set objAccess = Server.CreateObject("Access.Application.9")
objAccess.DBEngine.CompactDatabase "c:\folder\database.mdb", "c:\folder\backup_database.mdb"
Set objScript = Server.CreateObject("Scripting.FileSystemObject")
If objScript.FileExists("c:\folder\orig_database.mdb") Then
objScript.DeleteFile "c:\folder\orig_database.mdb"
End If
objScript.CopyFile "c:\folder\database.mdb", "c:\folder\orig_database.mdb", True
objScript.CopyFile "c:\folder\backup_database.mdb", "c:\folder\database.mdb", True
objScript.DeleteFile "c:\folder\orig_database.mdb"
Set objScript = Nothing
Set objAccess = Nothing
End Function
====================
Here's a breakdown of what happens:
(1) The file "database.mdb" is compacted into a new file called "backup_database.mdb"
(2) The script checks for "orig_database.mdb" and if it exists, it deletes it (It SHOULDN'T be there, but if the last time you ran this failed it would be).
(3) The script copies "database.mdb" (the original uncompacted one) to "orig_database.mdb".
(4) The script copies "backup_database.mdb" (the new one) to "database.mdb" (the original)
(5) The script deletes "orig_database.mdb"
TEST THIS on a dummy or copy of a database first before you put it into production, I take no liability here - it works for me, but make sure it will work for you first. Uncomment the objScript.DeleteFile lines while testing to make sure you don't delete your original database. You could also have it moved to an archive folder, etc. Whatever you want to do with it.
Also, don't run this at the end of scripts since users would be denied access to the DB while it was going on. Just run it once in a while when you think you need to and when the site is idle.
