MS Access Backup database

I am currently using a code to backup database, i have listed the code using below. I love the way this code works the only thing i want to be able to do is make a DB called Backup. on load it will compact and repair front-end of a DB and back up the back-end of the DB using the method of this code! The way my DB works is i.e Front-end DB location: C:\Quick Logs\Quick Logs V1.101.mde, back-end location: C:\Quick Logs\Data\Quick Logs_be.mdb. I want the backup of the back-end to be placed in the same folder location as the back-end, but like this: C:\Quick Logs\Data\Backups\Quick Logs_be, 02-21-2011.mdb.

Option Explicit
Option Compare Database


Public Function BackupDB()
'Requires a reference to the Microsoft Scripting Runtime library

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\"
   
   '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")
         ![SaveNumber] = SaveNo
         .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

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 

Open in new window

Blueice13085Asked:
Who is Participating?
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
Maybe this is what you want.:

Backup, Repair, and Compact a List

CAL (Compact A List) is an MS Access database that eases your backup and database maintenance tasks by allowing you to backup, repair, and compact a saved list of MS Access database files. It also saves the results of these actions in a table for later viewing, making it suitable for batch or scheduled execution. It's simple and easy to use. Just specify your list of MS Access database files, choose your execution options, and click a button to begin, or use a shortcut, or scheduler like Windows Task Scheduler to automatically process the list of files.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So your code works, but you just want to prepend the Date to the backup name?

If so, then do this:

strDayPrefix = Month(Now) & Day(Now) & Year(Now)
0
 
Blueice13085Author Commented:
no please read my question, and then read how the code works. thanks
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I did read your question. It was difficult to follow, which was the reason I asked the question.



0
 
Blueice13085Author Commented:
what i want the code to do is point to a DB by name and location instead of current DB,
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm still not sure exactly what you mean, but you can refer to a Database by the full path, of course. For example, in this code line:

Set dbs = CurrentDb

If you instead wish to set the value of "dbs" to something other than the CurrentDB object, you can do this:

Set dbs = DAO.OpenDatabase("Full path to the database")

Assuming you have the needed permissions to do so, the dbs variable would open that database, and you could use the "dbs" variable just as you would CurrentDB.

Similarly, if you're trying to define a different path with this:

strBackupPath = Application.CurrentProject.path & "\Backups\"

You can just write out the full path, as needed, like this:

strBackupPath = "C:\SomeFolder\Backups\"

If this doesn't address your issue, please take a moment to reform your question and post back here.
0
 
Rey Obrero (Capricorn1)Commented:
if i read it right, the OP wants to have a third db (named BackUp) that on load

* will compact and repair the FE
* will backup the BE
   backup copy will be on the same folder as the BE with a date in the name.

0
 
Blueice13085Author Commented:
capricorn1: that is exactly what i am needing!
0
 
Blueice13085Author Commented:
LSMConsulting: what if on this line
Set dbs = DAO.OpenDatabase("Full path to the database")

there is a password on that database
0
 
Blueice13085Author Commented:
LSMConsulting:
 the code below works to back up the database, the only problem is it only works seems like its trying to read the tblbackupinfo from both DB's this code is in a DB by its self, will only do one back up correct. should do total of 5 and then start all over again coping over the first one etc!

Option Explicit
Option Compare Database


Public Function BackupDB()
'Created by Helen Feddema 7-29-2003
'Last modified 3-Jan-2005
'Requires a reference to the Microsoft Scripting Runtime library

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 = "C:\Quick Logs\Data\Quick Logs_be.mdb"
   Debug.Print "Current db: " & strCurrentDB
   strBackupPath = "C:\Quick Logs\Data\Backup\"
   
   '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 = DAO.OpenDatabase("C:\Quick Logs\Data\Quick Logs_be.mdb", _
 False, False, "MS Access;PWD=password")
      Set rst = dbs.OpenRecordset("tblBackupInfo")
      With rst
         .AddNew
         ![SaveDate] = Format(DATE, "d-mmm-yyyy")
         ![SaveNumber] = SaveNo
         .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

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

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Here's an article about using OpenDatabase for password-protected databases:

http://support.microsoft.com/kb/209953

But it looks like you've already got that straight :)

As to the second issue:

It would seem to me that you'd have to increment the value of tblBackupInfo.SaveNumber EVERY TIME you call the SaveNo function. I'm not sure what impact this would have, based on the Date criteria, but you could add this line to the SaveNo function:

  Currentdb.Execute "UPDATE tblBackupInfo SET SaveNumber=" & intDayNo + 1 & " WHERE SaveDate=Date()")
  SaveNo = strNextNo

If you want a MAXIMUM of 5, you'd probably need to trap for that in the SaveNo function as well:

intDayNo = Nz(DMax("[SaveNumber]", "tblBackupInfo", "[SaveDate] = Date()"))

If intDayNo > 5 Then
   '/rest the value in tblBackupInfo to 1, and return that
   Currentdb.Execute "UPDATE tblBackupInfo SET SaveNumber=1 WHERE SaveDate=Date()")
    SaveNo = 1
Else
  strNextNo = CStr(intDayNo + 1)
  SaveNo = strNextNo
End If





0
 
Blueice13085Author Commented:
LSMConsulting:

dont understand where to put this code that you  sent? kinda mixed up.

i also made a .bat file for this to be done on a scheduled time each day

@echo off

REM Cannot make a valid copy while database is in use
if Exist "C:\Quick Logs\Data\Quick Logs_be.ldb" Goto DatabaseInUse

REM Create the backup folder in case it does not already exist.
mkdir "C:\Quick Logs\Data\Backups"
copy "C:\Quick Logs\Data\Quick Logs_be.mdb" "C:\Quick Logs\Data\Backups\"
REM Let the user see the results. NOTE: Do NOT do this in a scheduled task.
pause
exit

echo ERROR: Cannot copy database file while program is being used.
pause
exit

Open in new window


Also still the compact and repair problem
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Let's backup a bit.

Exactly what are you trying to accomplish? So far we've discussed 5 different issues:

1) Backing up one or more databases
2) Pointing to a database by location, rather than by CurrentDB
3) Opening a Password protected database
4) Apparently limiting the number of backups per day to 5 (not sure about this one)
5) Running a batch file

So let's start back at the beginning:

What are you trying to do?



0
 
Helen FeddemaConnect With a Mentor Commented:
That code is from an old Access Archon article of mine -- you might be interested in a updated version, with lots more choices, in my Extras Plus add-in, which can be downloaded from this link:

http://www.helenfeddema.com/Files/code20.zip

Now that the code is in the add-in, it can be used from any database.

Here is the add-ins Options screen:
Extras-Plus-Options-dialog.jpg
0
All Courses

From novice to tech pro — start learning today.