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
Solved

How to run nightly backup of ms access 2007 database tables only

Posted on 2010-11-18
11
572 Views
Last Modified: 2012-05-10
Hi, I have a database.  I have seen another system in which the programmer set  the db to create a backup of itself on the drive nightly.  How can I set this? Thanks
0
Comment
Question by:LadyHagood
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 57
ID: 34165997

  A JET/ACE database can't really backup itself because you can't backup a open file accurately.

  What you need is another DB that has code to perform the backup (which would be a simple file copy), a batch file, or use some type of backup utility.

JimD.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 34166135
try this codes, place in a regular module



Option Compare Database
Option Explicit

Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

Public Function fnc_CopyFile(ByVal strCurrentDB As String, ByVal strBackUpCopy As String) As Boolean

    Dim ret As Long

    On Error Resume Next
   
    DoCmd.Hourglass True
    ret = CopyFile(strCurrentDB, strBackUpCopy, True)
    If ret = 0 Then
        MsgBox "File Copy failed.", vbCritical, "File Copy"
        fnc_CopyFile = False
    Else
        fnc_CopyFile = True
    End If
    DoCmd.Hourglass False
End Function

to use, pass the path and name of the currentdb and the path and name of the backup copy

fnc_CopyFile(pathOfcurrentdb,pathofbackupcopy)

Open in new window

0
 
LVL 7

Accepted Solution

by:
shaydie earned 250 total points
ID: 34166373
Another approach.. if you only want to backup the tables and not the entire db.. It will take some modification.

Place the code (Function) below in a module, Create a query for each table data you want to backup, in the code change the 'LocalQueryNameTable?'s to the name of the queries and the 'BackupTable?'s to the name of the backed up tables, add entries as needed for each table to be backed up, change the Path ("S:\Backup\DataBase\") to the actual path of where you want the backups stored.

Create a macro that calls the DBBackup function and then exits the database.

Create a batch file to open the db and run the macro you created.
Something like this:

"C:\Program Files\Microsoft Office\Office12\msaccess.exe" "Path to your Database" /x MacroName


Setup a scheduled task to run the batch file you created.





Public Function DBBackup()
    Set wrkDefault = DBEngine.Workspaces(0)
    Set DB = CurrentDb
   
date1 = Format(Date, "DDMMMYYYY")

        Set dbsNew = wrkDefault.CreateDatabase("S:\Backup\DataBase\" & date1 & "-DBBackup.mdb", dbLangGeneral)
        dbsNew.Close

    DoCmd.TransferDatabase acExport, "Microsoft Access", "S:\Backup\DataBase\" & date1 & "-DBBackup.mdb", acTable, "LocalQueryNameTable1", "BackupTable1"
    DoCmd.TransferDatabase acExport, "Microsoft Access", "S:\Backup\DataBase\" & date1 & "-DBBackup.mdb", acTable, "LocalQueryNameTable2", "BackupTable2"
    DoCmd.TransferDatabase acExport, "Microsoft Access", "S:\Backup\DataBase\" & date1 & "-DBBackup.mdb", acTable, "LocalQueryNameTable3", "BackupTable3"

End Function
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34166503


btw,
the codes a posted at http:#a34166135 is placed in the db and called in the same db that you want to backup..

to use, you can call the function from the click event of a button

private sub cmdBackUp_Click()
dim x  as integer
x = fnc_CopyFile(CurrentDb.Name, CurrentProject.Path & "\BackUp.mdb")
end sub
0
 
LVL 57
ID: 34166755
<<the codes a posted at http:#a34166135 is placed in the db and called in the same db that you want to backup..>>

  While that will work, it's not a hot idea.  If the DB has any activity during the backup, you may end up with an inconsistent backup.

  Only backup utlitities with a special open file option, which reach into the OS and ensures a quiet point and does a atomic move (ensures that the file is copied in it's entirety without any I/O to it occuring) will give you a consistent backup on an open file.

JimD.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34166855
<If the DB has any activity during the backup, > 
i don't think, someone with a sane mind will backup a db that is not idle..
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34169545

<i don't think, someone with a sane mind will backup a db that is not idle..>
The issue is that the OP did not state if the DB might be open or not.

besides, as you know, there are dozens of posts here where people try just that...
;-)

Jeff
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34169555
...and as a developer, you should prevent that from happening.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34169590
LadyHagood,

<I have seen another system in which the programmer set  the db to create a backup of itself on the drive nightly.>
Then can you ask that programmer what they did?

The other issue here is the state of the machine that will be doing the backup.
Can you be sure that this machine will be On?
(Not, Hibernating, Asleep or on Standby)

Suppose a night gets skipped for some reason, is this OK?
Suppose the Backup fails or is incomplete?

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34169641
<...and as a developer, you should prevent that from happening.>
...It is not clear if the OP is a Developer or just a person being asked to do a backup.

My point was that you can be perfectly sane and make a mistake about something you don't know.

For example what I first started driving, I was "Sane" but I did not know you had to check the oil in your engine.  So I didn't and the engine blew.

Now, you could argue that no sane person would drive a car for months and never check the oil, but in this situation I did not know.

The same could apply here.
It's just that a comment like:
<i don't think, someone with a sane mind will backup a db that is not idle..>
...implies that if a person did this, that they were "not" sane...

;-)

Jeff




0
 
LVL 75
ID: 34171000
"How to run nightly backup of ms access 2007 database tables only"
                                                                            ^^^^^^^^^^^^^^^^^^^^
LadyHagood:
Let me ask a question:
Is this "access 2007 database tables only" ... a  'back end' db that only has tables and nothing else?  

If so, then I would have to ask how ... the CopyFile solution present above is going 'run itself' ... per your statement "create a backup of itself" ?

And clicking a command button to run that code would imply that ... you are 'manually' in that db to do so.  And if that is the case, then yes, it will back up the db ... which eliminates the issue of

"If the DB has any activity during the backup, you may end up with an inconsistent backup." ...

because that *is* the 'activity' ... executing the code to backup the db you are in.  No other activity is going to occur while that code is execution.   Well ... unless there are some front ends connected to this back end ... and someone is writing data at that moment.  Who knows what would happen then ??  And even if this is still a standalone db, it's not really going to back 'itself' up, short of some Timer scheme that triggers at a specific time and runs the CopyFile code.  But that would imply that someone has the db open, and they could be doing some 'activity' that might would interfere with the CopyFile process.

So, this all seems a bit ... convoluted to me ...

mx






0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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