By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,462 Members | 1,126 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,462 IT Pros & Developers. It's quick & easy.

Automated Backup of Database Backs up Linked Tables

100+
P: 106
Hi,

with the code below, I was successfully backing up my database regularly until I separated the Front end and used linked tables. Now only the linked tables are being backed up. (Not much good)!

how can I reference my BackEnd Tables Database 'acc.mdb' and create an automated copy.

Expand|Select|Wrap|Line Numbers
  1. Private Sub BackupData()
  2.  
  3.     Dim sFile As String
  4.     Dim objectDB As DAO.Database
  5.     Dim oTbl As TableDef
  6.  
  7.     sFile = "s:\acc_" & Format(Date, "ddmmyyyy") & ".mdb"
  8.     If Dir(sFile) <> "" Then Kill sFile
  9.  
  10.     Set objectDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
  11.     objectDB.Close
  12.  
  13.     DoCmd.Hourglass True
  14.     For Each oTbl In CurrentDb.TableDefs
  15.         If Left(oTbl.Name, 4) <> "msys" Then
  16.         DoCmd.CopyObject sFile, , acTable, oTbl.Name
  17.         End If
  18.         'DoCmd.TransferDatabase acExport, "Microsoft Access", sFile, acTable, oTD.Name
  19.     Next oTbl
  20.     DoCmd.Hourglass False
  21.  
  22. End Sub
I have tried adding the following code to make the BE_Tables database as CurrentDatabase, but it did not work.

Expand|Select|Wrap|Line Numbers
  1.  
  2. ...
  3.     Dim oDB2 As dao.Database
  4.     Dim sData As String
  5.     sData = "s:\acc786_data.mdb"
  6.     Set oDB2 = DBEngine.Workspaces(0).OpenDatabase(sData)
  7.  
  8.     DoCmd.Hourglass True
  9.     For Each oTbl In oDB2.TableDefs
  10. ...
  11.  
Help is greatly appreciated.
Jun 25 '14 #1

✓ answered by twinnyfo

tasawer,

I'm not sure if this is along the line of what you are interested in.

In my database, I use a splash form that automatically backs up my file(s), dates them, and deletes any files older than 30 days. This has worked reliably for me for several years without a single fail.

I put this into my splash form in the OnTimer Event (initial Timer Interval set to 1):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2. On Error GoTo EH
  3.     Dim strDBASEDATA as String
  4.     Dim strBACKUPPATH as String
  5.     Dim strDate As String
  6.     Dim strFile1 As String
  7.     Dim strFile2 As String
  8.     Dim FSO
  9.     Dim strSQL as String
  10.     Dim strFileName As String
  11.  
  12.     Me.txtLastBackup = DLookup("[LastBackup]", "tblBackupDate")
  13.  
  14.     'Make Backup of Database
  15.     If Me.txtLastBackup < Now - 0.25 Then
  16.         'This will back up the file every time someone logs into the DB
  17.         'As long as it has been 6 hours since the last log in
  18.         strDBASEDATA = "\\DatabasePath\"
  19.         strBACKUPPATH = "\\BackupPathpath\"
  20.         strDate = Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hh-mm")
  21.         strFile1 = strDBASEDATA & "DatbaseName.accdb"
  22.         strFile2 = strBACKUPPATH & DatabaseName BACKUP - " & _
  23.             strDate & ".accdb"
  24.         Set FSO = CreateObject("Scripting.FileSystemObject")
  25.         FSO.CopyFile strFile1, strFile2
  26.         Me.txtLastBackup = Now
  27.         strSQL = "UPDATE tblBackupDate SET" & _
  28.             " tblBackupDate.LastBackup = #" & Now & "#;"
  29.         DoCmd.SetWarnings False
  30.         DoCmd.RunSQL strSQL
  31.         DoCmd.SetWarnings True
  32.     End If
  33.     'Delete old Backup Files
  34.     strFileName = Dir(strBACKUPPATH)
  35.     Do While strFileName <> ""
  36.         If Date - 30 > CDate(Mid(strFileName, Len(strFileName) - 21, 10)) Then
  37.             Kill strBACKUPPATH & strFileName
  38.         End If
  39.         strFileName = Dir()
  40.     Loop
  41.     TimerInterval = 0
  42.     Exit Sub
  43. EH:
  44.     If Err.Number = 13 Then
  45.         'My Backup path should have nothing but backup files in it
  46.         'However, if there is a stray file without the standard
  47.         'naming convention just move along to the next file
  48.         Resume Next
  49.     Else
  50.         MsgBox "There was an error backing up Database!  " & _
  51.             "Please contact your Database Administrator.", vbCritical, "Error!"
  52.         Resume Next
  53.     End If
  54. End Sub
I also have a Table, with one record and one field, which houses the last backup date. Then, there is a text box on the form that displays that date/time for the user.

I hope this helps you along your way!

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,768
I'm not sure if there's a more straightforward way but certainly Application Automation would enable you to do what you need with the BE.

I'm fairly sure that Copy/Paste now gives the option to paste as a local table nowadays though. Have you looked into that?
Jun 25 '14 #2

twinnyfo
Expert Mod 2.5K+
P: 3,482
tasawer,

I'm not sure if this is along the line of what you are interested in.

In my database, I use a splash form that automatically backs up my file(s), dates them, and deletes any files older than 30 days. This has worked reliably for me for several years without a single fail.

I put this into my splash form in the OnTimer Event (initial Timer Interval set to 1):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2. On Error GoTo EH
  3.     Dim strDBASEDATA as String
  4.     Dim strBACKUPPATH as String
  5.     Dim strDate As String
  6.     Dim strFile1 As String
  7.     Dim strFile2 As String
  8.     Dim FSO
  9.     Dim strSQL as String
  10.     Dim strFileName As String
  11.  
  12.     Me.txtLastBackup = DLookup("[LastBackup]", "tblBackupDate")
  13.  
  14.     'Make Backup of Database
  15.     If Me.txtLastBackup < Now - 0.25 Then
  16.         'This will back up the file every time someone logs into the DB
  17.         'As long as it has been 6 hours since the last log in
  18.         strDBASEDATA = "\\DatabasePath\"
  19.         strBACKUPPATH = "\\BackupPathpath\"
  20.         strDate = Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hh-mm")
  21.         strFile1 = strDBASEDATA & "DatbaseName.accdb"
  22.         strFile2 = strBACKUPPATH & DatabaseName BACKUP - " & _
  23.             strDate & ".accdb"
  24.         Set FSO = CreateObject("Scripting.FileSystemObject")
  25.         FSO.CopyFile strFile1, strFile2
  26.         Me.txtLastBackup = Now
  27.         strSQL = "UPDATE tblBackupDate SET" & _
  28.             " tblBackupDate.LastBackup = #" & Now & "#;"
  29.         DoCmd.SetWarnings False
  30.         DoCmd.RunSQL strSQL
  31.         DoCmd.SetWarnings True
  32.     End If
  33.     'Delete old Backup Files
  34.     strFileName = Dir(strBACKUPPATH)
  35.     Do While strFileName <> ""
  36.         If Date - 30 > CDate(Mid(strFileName, Len(strFileName) - 21, 10)) Then
  37.             Kill strBACKUPPATH & strFileName
  38.         End If
  39.         strFileName = Dir()
  40.     Loop
  41.     TimerInterval = 0
  42.     Exit Sub
  43. EH:
  44.     If Err.Number = 13 Then
  45.         'My Backup path should have nothing but backup files in it
  46.         'However, if there is a stray file without the standard
  47.         'naming convention just move along to the next file
  48.         Resume Next
  49.     Else
  50.         MsgBox "There was an error backing up Database!  " & _
  51.             "Please contact your Database Administrator.", vbCritical, "Error!"
  52.         Resume Next
  53.     End If
  54. End Sub
I also have a Table, with one record and one field, which houses the last backup date. Then, there is a text box on the form that displays that date/time for the user.

I hope this helps you along your way!
Jun 25 '14 #3

100+
P: 106
Thank You Twinnyfo. This has worked a treat.
Jun 25 '14 #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
Wonderful! I'm glad I could help out! Let us know if you have any other questions.
Jun 25 '14 #5

P: 1
When you set the variables of your backup path. Make sure it's not in the If statement to check when the last backup was. Otherwise it doesn't get set correctly when you go through the delete backups loop if the last backup was less than 6 hours ago and it will delete files from the default directory opened.
Sep 30 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.