Code Snippets

In all the times I have spent participating in forum discussions about all sorts of users' questions, I have learnt a great deal from all the participants.

I have decided to post some of the sample codes that I found users were asking for almost all the time to help me keep them in one place and to make it easier for me to share them again and again.

Hope you find them useful.



Backup and Compact BE

I learned this one from fellow MVP, Brent Spaulding (datAdrenaline). It creates a backup copy of the BE and compacts it.

Note: Replace "tblHidden" with the name of one of your linked tables in the BE and replace "\SCR_BE" with the actual name of your BE file.

Updates

Added code to handle password-protected backend files.

Public Sub BackUpAndCompactBE()
'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Modified by theDBguy on 5/27/2019
'Source: http://www.accessmvp.com/thedbguy

On Error GoTo errHandler

    Dim oFSO As Object
    Dim strDestination As String
    Dim strSource As String
    Dim strTableName As String
    Dim strFileName As String

    strTablename = "tblHidden" 'name of your linked table
    strFileName = "\SCR_BE" 'name of your backup file

    'Get the source of your back end
    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _
    		"Database=")(1), ";")(0)
    
    'Determine your destination
    strDestination = CurrentProject.Path & strFileName & " (" _
    		& Format(Now, "yyyymmddhhnnss") & ").accdb"
    
    'Flush the cache of the current database
    DBEngine.Idle
    
    'Create a file scripting object that will backup the db
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.CopyFile strSource, strDestination
    Set oFSO = Nothing
    
    'Compact the new file, ...
    Name strDestination As strDestination & ".cpk"
    DBEngine.CompactDatabase strDestination & ".cpk", strDestination
    'Uncomment the following line and comment the previous line 
    'if your backend file is password protected or if you want the backup to have a password
    'DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=YourPassword"
    Kill strDestination & ".cpk"
    
    'Notify users
    MsgBox "Backup file '" & strDestination & "' has been created.", _
    		vbInformation, "Backup Completed!"
    
errExit:
    Exit Sub
    
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit
    
End Sub