MS Access: how to compact current database in VBA

2019-01-06 18:02发布

Pretty simple question, I know.

13条回答
该账号已被封号
2楼-- · 2019-01-06 18:27

Check out this solution VBA Compact Current Database.

Basically it says this should work

Public Sub CompactDB() 
    CommandBars("Menu Bar").Controls("Tools").Controls ("Database utilities"). _
    Controls("Compact and repair database...").accDoDefaultAction 
End Sub 
查看更多
姐就是有狂的资本
3楼-- · 2019-01-06 18:28

DBEngine.CompactDatabase source, dest

查看更多
混吃等死
4楼-- · 2019-01-06 18:28

There's also Michael Kaplan's SOON ("Shut One, Open New") add-in. You'd have to chain it, but it's one way to do this.

I can't say I've had much reason to ever want to do this programatically, since I'm programming for end users, and they are never using anything but the front end in the Access user interface, and there's no reason to regularly compact a properly-designed front end.

查看更多
我命由我不由天
5楼-- · 2019-01-06 18:29

If you have the database with a front end and a back end. You can use the following code on the main form of your front end main navigation form:

Dim sDataFile As String, sDataFileTemp As String, sDataFileBackup As String
Dim s1 As Long, s2 As Long

sDataFile = "C:\MyDataFile.mdb"
sDataFileTemp = "C:\MyDataFileTemp.mdb"
sDataFileBackup = "C:\MyDataFile Backup " & Format(Now, "YYYY-MM-DD HHMMSS") & ".mdb"

DoCmd.Hourglass True

'get file size before compact
Open sDataFile For Binary As #1
s1 = LOF(1)
Close #1

'backup data file
FileCopy sDataFile, sDataFileBackup

'only proceed if data file exists
If Dir(sDataFileBackup vbNormal) <> "" Then

        'compact data file to temp file
        On Error Resume Next
        Kill sDataFileTemp
        On Error GoTo 0
        DBEngine.CompactDatabase sDataFile, sDataFileTemp

        If Dir(sDataFileTemp, vbNormal) <> "" Then
            'delete old data file data file
            Kill sDataFile

            'copy temp file to data file
            FileCopy sDataFileTemp, sDataFile

            'get file size after compact
            Open sDataFile For Binary As #1
            s2 = LOF(1)
            Close #1

            DoCmd.Hourglass False
            MsgBox "Compact complete " & vbCrLf & vbCrLf _
                & "Size before: " & Round(s1 / 1024 / 1024, 2) & "Mb" & vbCrLf _
                & "Size after:    " & Round(s2 / 1024 / 1024, 2) & "Mb", vbInformation
        Else
            DoCmd.Hourglass False
            MsgBox "ERROR: Unable to compact data file"
        End If

Else
        DoCmd.Hourglass False
        MsgBox "ERROR: Unable to backup data file"
End If

DoCmd.Hourglass False
查看更多
Rolldiameter
6楼-- · 2019-01-06 18:32

For Access 2013, you could just do

Sendkeys "%fic"

This is the same as typing ALT, F, I, C on your keyboard.

It's probably a different sequence of letters for different versions, but the "%" symbol means "ALT", so keep that in the code. you may just need to change the letters, depending on what letters appear when you press ALT

Letters that appear when pressing ALT in Access 2013

查看更多
来,给爷笑一个
7楼-- · 2019-01-06 18:44

Yes it is simple to do.

Sub CompactRepair()
  Dim control As Office.CommandBarControl
  Set control = CommandBars.FindControl( Id:=2071 )
  control.accDoDefaultAction
End Sub

Basically it just finds the "Compact and repair" menuitem and clicks it, programatically.

查看更多
登录 后发表回答