Results 1 to 2 of 2
  1. #1
    Neil Bingham is offline Novice
    Windows XP Access 2000
    Join Date
    Feb 2009
    Location
    Cumbria UK
    Posts
    14

    Automating Access Database Back-up from laptop to base desktop when I close database

    I need to be a able to always see up to date data on either desktop or laptop but generally use the laptop for entering data. At present (providing I remember!) when using laptop, I close database then copy to a folder on desktop and select the option to overwrite the previous one already there. How can I automate this please? Using Office 2010 and Win7 on both pcs

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For clarification:

    You have 2 copies of the database. 1 on desktop and 1 on laptop. And you are trying to keep these in sync.

    Here is some code I got many years ago. It was in some material from Helen Feddema.

    You can see if it applies. You could invoke the code from a button ( or similar).

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : BackupDB
    ' DateTime  : 2005-11-04 13:50
    ' Author    : jed
    ' Purpose   : From AccessD  Woody Access Watch
    ' To backup an MDB to a known location.
    '---------------------------------------------------------------------------------------
    '
    Public Function BackupDB()
    'Created by Helen Feddema 7-29-2003
    'Last modified 3-Jan-2005
    'Requires a reference to the Microsoft Scripting Runtime library
    '
    'jed
    ' Must set security properties of the database file mdb (inherit)
    ' else you'll get permission error #70
    
    On Error GoTo ErrorHandler
       
       Dim dbs As DAO.Database
       Dim rst As DAO.Recordset
       Dim strCurrentDB As String
       Dim fso As Scripting.FileSystemObject
       Dim strTitle As String
       Dim strPrompt As String
       Dim intReturn As Integer
       Dim strDayPrefix As String
       Dim strSaveName As String
       Dim strBackupPath As String
       Dim fld As Scripting.Folder
       
       Set fso = CreateObject("Scripting.FileSystemObject")
       strCurrentDB = Application.CurrentProject.FullName
       Debug.Print "Current db: " & strCurrentDB
       'strBackupPath = Application.CurrentProject.Path & "\Backups\" '<<---backup info
       strBackupPath = "C:\backup\" '<<<--------------------------------------backup info
       'Attempt to set a reference to the backup folder
       Set fld = fso.GetFolder(strBackupPath)
       strDayPrefix = Format(Date, "mm-dd-yyyy")
       strSaveName = Left(Application.CurrentProject.Name, _
          Len(Application.CurrentProject.Name) - 4) & " " & SaveNo _
          & ", " & strDayPrefix & ".mdb"
       strSaveName = strBackupPath & strSaveName
       Debug.Print "Backup save name: " & strSaveName
       strTitle = "Database backup"
       strPrompt = "Save database to " & strSaveName & "?"
       intReturn = MsgBox(strPrompt, vbYesNo, strTitle)
       If intReturn = vbYes Then
          Set dbs = CurrentDb
          Set rst = dbs.OpenRecordset("tblBackupInfo")
          With rst
             .AddNew
             ![SaveDate] = Format(Date, "d-mmm-yyyy")
             ![SaveTime] = Now
             ![SaveNumber] = SaveNo
             ![SaveCompName] = CompName
             ![SaveBkupName] = strSaveName
             .Update
             .Close
          End With
          fso.CopyFile strCurrentDB, strSaveName
       End If
          
    ErrorHandlerExit:
       Exit Function
    
    ErrorHandler:
       If Err.Number = 76 Then
          'If backup folder was not found, create it
          fso.CreateFolder strBackupPath
          Resume Next
       Else
          MsgBox "Error No: " & Err.Number & "; Description: " & _
             Err.Description
          Resume ErrorHandlerExit
       End If
       
    End Function
    Code:
    Public Function SaveNo() As String
    'Created by Helen Feddema 7-15-2003
    'Last modified 9-30-2004
    
    On Error GoTo ErrorHandler
       
       Dim intDayNo As Integer
       Dim strNextNo As String
       
       'Create unique save number for today
       intDayNo = Nz(DMax("[SaveNumber]", "tblBackupInfo", "[SaveDate] = Date()"))
       Debug.Print "Day no. " & intDayNo
       strNextNo = CStr(intDayNo + 1)
       Debug.Print "Next No. " & strNextNo
       SaveNo = strNextNo
       
    ErrorHandlerExit:
       Exit Function
    
    ErrorHandler:
       MsgBox "Error No: " & Err.Number & " Description: " & _
          Err.Description
       Resume ErrorHandlerExit
    
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-21-2013, 01:20 PM
  2. Replies: 4
    Last Post: 08-21-2013, 07:08 AM
  3. Replies: 1
    Last Post: 08-14-2013, 06:29 PM
  4. Replies: 5
    Last Post: 01-01-2013, 09:15 AM
  5. Replies: 18
    Last Post: 08-12-2009, 08:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums