Solved VBA Compact & Repair (1 Viewer)

[Romio_1968]

Is there any way to crete a Compact & Repair button and a module or macro to execute?
The user will not have access to Ribbon and nav Pane, so that's why the need.

 

[theDBguy]

What are you trying to compact, the FE or the BE?

 

[Romio_1968]

Trying to raplicate the whole behavior of C&R buildt in function

 

[theDBguy]

Trying to raplicate the whole behavior of C&R buildt in function

Yes, but on which file though?

 

[Romio_1968]

The buildt in overwrites the current file, while creating a backup

 

[theDBguy]

The buildt in overwrites the current file, while creating a backup

Is your database split?

 

[Romio_1968]

No

 

[Romio_1968]

A limitation may be possible here, i guess. Tryed and failed few times. I fond some indications on net that the process may be prohibited, others are saying that it is possible... nothing worked

 

[theDBguy]

No

Any reason why not? Just curious...

 

[theDBguy]

A limitation may be possible here, i guess. Tryed and failed few times. I fond some indications on net that the process may be prohibited, others are saying that it is possible... nothing worked

I have a solution for both FE and BE, but only for MDB when it's not split.

 

[Romio_1968]

Using Access 2019

Sub CompactAndRepairDB()
    Dim strDBName As String
    strDBName = CurrentDb.Name
    Application.CompactRepair strDBName, strDBName
    ' Delete the original database file
    Kill strDBName
    ' Rename the backup file to the original database name
    Name strDBName & "_backup" As strDBName
    MsgBox "Database has been compacted and repaired."
End Sub

This runs into 7847 runtime error

Public Function CompactAndRepairDB()
    Dim strDBName As String
    Dim strBackupPath As String
    
    ' Get the name of the current database
    strDBName = CurrentDb.Name
    
    ' Set the path for the backup file to the user's desktop
    strBackupPath = Environ("USERPROFILE") & "\Desktop\" & Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh-mm-ss") & " backup.accdb"
    
    ' Compact and repair the database
    Application.CompactRepair strDBName, strBackupPath
    
    ' Display a message when the process is complete
    MsgBox "Baza de date a fost compactata si reparata" & vbNewLine & vbNewLine & _
           "O copie de rezerva a fost plasata pe Desktop." & vbNewLine & vbNewLine & _
           "Puteti sterge copia dupa ce va asigurati ca baza de date este functionala.", vbInformation, ""

End Function

This pops the 7846

 

[Josef P.]

Is there any way to crete a Compact & Repair button and a module or macro to execute?
The user will not have access to Ribbon and nav Pane, so that's why the need.

Would a command line switch also help?

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" %1 /compact

 

[Romio_1968]

Would a command line switch also help?

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" %1 /compact

What is that?

 

[Josef P.]

Command-line switches for Microsoft Office products - Microsoft Support

Use command-line switches to customize the startup process for Microsott Office programs: Word, Excel, PowerPoint, Outlook, and Access.

support.microsoft.com

 

[theDBguy]

Check out this sample code.

Code Snippets

 

[Romio_1968]

Code Snippets

 

[theDBguy]

View attachment 106490

You'll have to use the name of your linked table. Remember, that code is for a split database. If your database is not split, you could try adjusting the code. The main piece of code is the DBEngine method.

 

[isladogs]

The ability to compact the current database using code was removed in A2010.
I believe this is because there is a risk of corruption...as can happen using the compact on close option

Either compact using the ribbon or do so externally from another database or using a command line switch

Access Command Line Switches

This article lists the command-line switch options available for Microsoft Access.

isladogs.co.uk

 

[Romio_1968]

The ability to compact the current database using code was removed in A2010.
I believe this is because there is a risk of corruption...as can happen using the compact on close option

Either compact using the ribbon or do so externally from another database or using a command line switch

Access Command Line Switches

This article lists the command-line switch options available for Microsoft Access.

isladogs.co.uk

I'm affraid that this is the answer.

 

[isladogs]

Neither of the examples you supplied will work.
You can use VBA to copy the current database to another file then compact that.
However you cannot overwrite the current file with the compacted copy whilst the original file is open