Access Howto:

How can I do an automated back-up of my attached Back-End db using FileCopy?

This function will automatically backup any attached Back-end database. It can be modified to work with multiple back-ends as well, contact me if you need help. The function below backs up to the a: drive, however you can back up to any drive that will allow writing, this includes network drives as well, just use the UNC path. It also works great to Zips and Jazz's and such.

The function as is, will overwrite the existing file every time. If you want a new backup every day, simply un-comment the appropriate lines, and comment out the one strDest value line, and it will write a file in the following format each time:

TodaysDate_FileName.mdb (i.e.  052600_MyBackEnd.mdb)

Simply copy the following sub routine into a module, replace the appropriate text, and you are ready to roll.

On the On Click event of a button simply type "Call Backup". That's It !!!

Hope it helps you.

==============================

Public Sub BackUp()
    'This function backs up to the a: drive, you can back up to any location, just change the strDest Value

On Error GoTo Err_Backup    
    Dim db As Database
    Dim strSource As String, strDest As String, strError As String
    Dim strDate As String, strDateX As String
    
    If MsgBox("Are you sure you want to back up data?", vbQuestion + vbYesNo, " Continue?") = vbYes Then
        ' Un-comment the following 3 lines for a new backup for every day
        'strDate = Format(Date, "mm/dd/yy")
        'strDateX = Left(strDate, 2) & Mid(strDate, 4, 2) & Right(strDate, 2)
        'strDest = "a:\" & strDateX
    
        Set db = CurrentDb()
        DoCmd.Hourglass True
        
        'Put any table name in here that exists in your back-end
        strSource = db.TableDefs("TableNameHere").Connect
        strSource = Mid(strSource, 11, Len(strSource) - 10)
        
        ' If you are using a new back-up every day, un-comment this line and replace the database name, and comment out the next line down
        'strDest = strDest & "_YourBackEndDBNameHere.mdb"
        
        ' Replace with your database Back-end name and different drive letter or UNC path if desired
        strDest = "a:\YourBackEndDBNameHere.mdb"
        
        FileCopy strSource, strDest
        
        db.Close
        
        DoCmd.Hourglass False
        MsgBox ("Backup Complete")
    End If
    
Exit_Backup:
    Exit Sub

Err_Backup:
    Select Case Err.Number
        Case 61
            strError = "Floppy disk is full" & vbNewLine & "cannot export mdb"
            MsgBox strError, vbCritical, " Disk Full"
            Kill strDest
        Case 70
            strError = "File is open" & vbNewLine & "cannot export mdb"
            MsgBox strError, vbCritical, " File Open"
        Case 71
            strError = "No disk in drive" & vbNewLine & "please insert disk"
            MsgBox strError, vbCritical, " No Disk"
        Case Else
            Err.Raise Err.Number, Err.Description
    End Select
    
    DoCmd.Hourglass False
    Resume Exit_Backup
    
End Sub

==========================

Tables: Recover records from a corrupt table

(Q) How can I recover records from a corrupt table?

(A) First, using Access while in the database window, copy the table's structure only from the corrupt table, if possible. Then using the code below, copy each individual row from the old table to the new one until it encounters an error. The error routine displays the error, skips one row, moves to the next and Resumes at Addit where it continues to move data from the old table to the new table one row at a time.

'  ********* Code Start ***********
' This code was originally written by Norm Chezem
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
' Code Courtesy of

' Norm Chezem

Function CopyRes()
  Dim db As Database
  Dim OldRes As Recordset
  Dim NewRes As Recordset
  Dim ErrMsg1 As String
  Dim RecCount As Long

  On Error GoTo err_Proc
  Set db = CurrentDb()
  Set OldRes = db.OpenRecordset("tbl_Reservations")
  Set NewRes = db.OpenRecordset("tbl_New_Res")
  RecCount = 0

  OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:

  NewRes.AddNew
  NewRes![ResID] = OldRes![ResID]

  'CONTINUE COPYING ALL ROWS FROM OLD TBL TO NEW
  NewRes.Update
  RecCount = RecCount + 1
  DoEvents
  If RecCount Mod 10000 = 0 then
    MsgBox RecCount 'Show progress every 10,000 rows
  End If
Loop

  MsgBox RecCount 'Show total successful record count
  OldRes.Close

  NewRes.Close

  db.close
Proc_Exit:
  Exit Function

Err_Proc:
  MsgBox "<Error>" & Error$
  OldRes.MoveNext 'Skip this corrupt row
  Resume Addit    'Continue at Addit
End Function

'  ********* Code End ***********