Backup the backend and compact it (AC2007) (1 Viewer)

[AOB]

Hi guys,

I'm putting together a subroutine to backup the backend of a split DB and compact it. Fairly straightforward; I use FSO to make a copy of the backend file to a separate location (defined with the objBackupFolder object), suffix the name with "Backup" and the date (so I can keep backups for a set period before deleting them), then compact the backup copy using the .tmp file extension, delete the (uncompacted) copy and rename the compacted file back to .accdb.

Here's how it looks at the moment (edited down for clarity) :

[FONT=Courier New]Public Sub BackupBackEnd()[/FONT]
 
[FONT=Courier New]    Dim objFSO As Object[/FONT]
[FONT=Courier New]    Dim objBackupFolder As Object[/FONT]
[FONT=Courier New]    Dim strBackEndDatabase As String[/FONT]
[FONT=Courier New]    Dim strBackEndDatabaseCopy As String[/FONT]
 
[FONT=Courier New]    Set objFSO = CreateObject("Scripting.FileSystemObject")[/FONT]
 
[FONT=Courier New]    ' Make a copy of the backend[/FONT]
 
[FONT=Courier New]    strBackEndDatabaseCopy = objBackupFolder.Path & "\" & Replace(Dir$(strBackEndDatabase), ".accdb", " Backup " & Format(Now, "dd mmm yyyy") & ".accdb")[/FONT]
 
[FONT=Courier New]    objFSO.CopyFile strBackEndDatabase, strBackEndDatabaseCopy, True[/FONT]
 
[FONT=Courier New]    ' Compact the backup to a .tmp file[/FONT]
 
[FONT=Courier New][COLOR=red]    Application.CompactRepair strBackEndDatabaseCopy, strBackEndDatabaseCopy & ".tmp"[/COLOR][/FONT]
 
[FONT=Courier New]    ' Delete the uncompacted backup database[/FONT]
 
[FONT=Courier New]    objFSO.DeleteFile strBackEndDatabaseCopy[/FONT]
 
[FONT=Courier New]    ' Rename the compacted backup database[/FONT]
 
[FONT=Courier New]    Name strBackEndDatabaseCopy & ".tmp" As strBackEndDatabaseCopy[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

It works fine but I keep getting this warning message from Access :

Microsoft Office Access Security Notice

A potential security concern has been identified

This file might contain unsafe content that could harm your computer. Do you want to open this file or cancel the operation?

...at the line highlighted in red? (Twice - once for the new .accdb and once for the new .tmp)

Normally I like warnings and I'm pretty careful about switching them off (and switching them back on again as early as possible!) but I know these files are safe (I'm generating them!) so I'd like to circumvent the warning if possible?

I've tried using :

DoCmd.SetWarnings False

But it doesn't prevent the warnings?

Any suggestions? Or is it even possible?

Thanks

AOB

 

[pr2-eugin]

Why are you renaming the extension to .tmp?

 

[AOB]

Hi Paul,

That's generally what I do whenever I'm backing up via VBA. I was under the impression that it was generally good practice?

I have to backup the copy to somewhere - it's just easier to name it .tmp and then remove the extension afterwards. I could just as easily back it up to another .accdb file and then delete the uncompacted version. But I don't think that's the issue here - even doing it that way, I should still get that warning message (or should I?)

I could also perform the backup directly from the backend but I'm wary of that potentially resulting in a corruption. So my preference is to make a copy first, then perform the compact & repair on the copy (so if anything goes wrong, my original backend is still intact)

Make sense?

Thanks

AOB

 

[AOB]

Just to confirm, I switched the code so that I made a copy of the BE first, and then compacted that to another .accdb file and deleted the first (uncompacted) copy. Still getting the same warning...

 

[AOB]

Figured it out!

Just needed to replace this line :

[COLOR=black]Application.CompactRepair strBackEndDatabaseCopy, strBackEndDatabaseCopy & ".tmp"[/COLOR]

With this :

DBEngine.CompactDatabase strBackEndDatabaseCopy, strBackEndDatabaseCopy & ".tmp"

Don't even need to switch the warnings off, it runs without interruption

Hope it helps somebody else...