ACCESS Compacting and Size

[mdumka]

Hello, I am using an ACCESS DB for a coldfusion website.

I just compacted it and it went from 1.4 MB to 288 KB.

What causes a DB to bloat like this and is there a vay to compact and repair the db with a command?

Thanks

[MarcusJT]

This is caused by fragmentation, and it affects all databases. It's just that some databases defragment themselves automatically, or on a schedule.

The Access desktop application itself (version 2002 and above) does this whenever you open (or is it close?) a database, but not when you connect to the database file from within a script using ODBC/OLEDB drivers.

See here:
http://support.microsoft.com/default...b;EN-US;209769

You can trigger it yourself through code like this (very old code, but should work for any v2000+ MDBs):

'Compacts an Access 2000 DB
Sub CompactMDB(DBPath)
	Dim fso, Engine, strDBPath

	strDBPath = left(DBPath,instrrev(DBPath,"\"))

	Set Engine = Server.CreateObject("JRO.JetEngine")
	Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath, _
	"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & "temp.mdb"

	Set fso = Server.CreateObject("Scripting.FileSystemObject")
	fso.CopyFile strDBPath & "temp.mdb",dbpath
	fso.DeleteFile(strDBPath & "temp.mdb")

	Set fso = nothing
	Set Engine = nothing
End Sub