Solved

MS Access Backup database

Posted on 2011-02-21
14
401 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:Blueice13085
14 Comments
 
LVL 84
ID: 34951085
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
 

Author Comment

by:Blueice13085
ID: 34957056
no please read my question, and then read how the code works. thanks
0
 
LVL 84
ID: 34957422
I did read your question. It was difficult to follow, which was the reason I asked the question.



0
 

Author Comment

by:Blueice13085
ID: 34957946
what i want the code to do is point to a DB by name and location instead of current DB,
0
 
LVL 84
ID: 34960728
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34960782
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
 

Author Comment

by:Blueice13085
ID: 34965864
capricorn1: that is exactly what i am needing!
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Blueice13085
ID: 34965921
LSMConsulting: what if on this line
Set dbs = DAO.OpenDatabase("Full path to the database")

there is a password on that database
0
 

Author Comment

by:Blueice13085
ID: 34966114
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
 
LVL 84
ID: 34969840
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
 

Author Comment

by:Blueice13085
ID: 34970814
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
 
LVL 84
ID: 34972065
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
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 34972220
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
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 34984802
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now