SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ACCESS Compacting and Size

    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

  2. #2
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):
    Code:
    '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
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •