making a back up of access db in a vb command button

[biggvito]

hello, i was wondering if it is possible to have a command button in vb that when clicked will make a copy of a access database (computer.mdb) to say the A: drive. Also can a similar button be made to restore a .mdb to a certain location. if this is not possible can you recommend the best way to back up and retrive a database. Any help greatly appreciated.

 

[BruceHesher]

There are a couple of ways you can do this. You can simply use the filecopy command and give it any path/filename you want. To restore it you can again use the filecopy command. Or, you can use the compact database method of the database engine as follows:

Private Sub cmdBackup_Click()  
    Screen.MousePointer = vbHourglass
    On Error GoTo errhandler
    'close all DAO's to release resources
    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    For Each ws In Workspaces
        For Each db In ws.Databases
            For Each rs In db.Recordsets
                rs.Close
                Set rs = Nothing
            Next
            db.Close
            Set db = Nothing
        Next
        ws.Close
        Set ws = Nothing
    Next   
    'if a compacted version already exists kill it
    If Dir$(App.Path & &quot;\Compacted.mdb&quot;) <> &quot;&quot; Then
       Kill (App.Path & &quot;\Compacted.mdb&quot;)
    End If
    'compact the db (must first have been opened using OpenDatabase)
    DBEngine.CompactDatabase App.Path & &quot;\MyDB.mdb&quot;, App.Path & &quot;\Compacted.mdb&quot;
    If FileLen(App.Path & &quot;\Compacted.mdb&quot;) > 0 Then
       response = MsgBox(&quot;size in bytes&quot; & vbCrLf & &quot;before            after&quot; & vbCrLf & FileLen(App.Path & &quot;\MyDB.mdb&quot;) & &quot;         &quot; & FileLen(App.Path & &quot;\compacted.mdb&quot;), vbOKOnly, &quot;Compaction results&quot;)
       Kill App.Path & &quot;\MyDB.mdb&quot;
       FileCopy App.Path & &quot;\Compacted.mdb&quot;, App.Path & &quot;\MyDB.mdb&quot;
    Else
       MsgBox (&quot;Database did not compact properly, original left untouched&quot;)
    End If
    GoTo done
errhandler:
    MsgBox (Err.Number & &quot;  &quot; & Err.Description)
done:
    Screen.MousePointer = vbDefault
End Sub