June 25, 2006 at 7:03 pm
Hi,
I need to connect to up to 4 MS Access Databases and extract info from them, but I thought I would like to Repair and Compact them first.
Is there a recommended way to do this programtically?
I was hoping to do this within DTS.
June 26, 2006 at 5:49 am
Compact & repair isn't something you should need to do too often - if you do (need to), there is probably too much activity on the database.
I've always done it manually via the Access GUI.
June 26, 2006 at 8:57 am
You can pass a command line parameter to Access to compact it. Just use
msacess.exe "database name and location" /compact
That should do it.
June 27, 2006 at 3:35 pm
the JRO.JetEngine library from microsoft has a CompactDatabase method.
I have been using it for a long time - you just pass two connection strings to the method - source and destination. They can't be the same obviously.
You actually create a new Access DB file then delete the original and rename the new one to the original's name. You still need to have exclusive access to the database being compacted (open Mode = adModeShareExclusive).
Compact and repair from the Access UI menu does exactly that.
June 28, 2006 at 2:08 am
Another suggestion I've seen used.. Its a bit lazy, but Access 2003 has an option to Compact on Close. Its on Tools->Options, the General tab. Whenever users close the mdb it compacts automatically. Not too bad if the database isn't huge.
June 29, 2006 at 9:09 am
This code uses the file system object to compact an Access database on close if over 35Mb .
Public Sub ShowFileSize(filespec)
On Error GoTo Err_handler
Dim fs As Object
Dim f As Object
Dim s As Integer
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = (f.Size \ 1000000)
If s > 35 Then
If MsgBox("Your copy of MPS needs to be compacted to improve performance, this may take a minute. Do you want to compact now?", vbYesNo) = vbYes Then
SetOption "Auto Compact", True
Else
SetOption "Auto Compact", False
End If
Else
SetOption "Auto Compact", False
End If
Err_handler:
End Sub
June 16, 2010 at 6:47 am
oldgoat (6/26/2006)
Compact & repair isn't something you should need to do too often - if you do (need to), there is probably too much activity on the database.I've always done it manually via the Access GUI.
That's not right; compact and repair on a JET/ACE DB should be done on a regular basis as a normal maintenance item. Beside reclaiming deleted space, it also reorg's the indexes, recalculates the DB statistics used in query costing, and rewrites table data to contiguous pages.
Jim.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply