SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
Thread: ACCESS Compacting and Size
Hybrid View
-
Apr 30, 2004, 10:43 #1
- 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
-
May 3, 2004, 15:13 #2
- 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