?
Solved

MS Access Backup database

Posted on 2011-02-21
14
Medium Priority
?
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 85
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 85
ID: 34957422
I did read your question. It was difficult to follow, which was the reason I asked the question.



0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 85
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 85
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 85
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 1000 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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 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