Microsoft Access Database Backup 1

[Unami]

My client wants to backup his Access Database with something as simple as clicking a button that will be on the Switchboard. He doesn't want to copy and paste. Is there a macro I can use or some simple codeing to do this?

 

[natatbh]

What doestn't you client want?
Doesn't he want to manually copy/paste or doesn't he want to use a copy/paste method for backup at all?

 

[Unami]

On the Switchboard he just wants to click a button "Backup" and it will be done. He doesn't want to copy and paste. So I have to write a macro and codeing to support this event.

 

[natatbh]

Use the fileCopy function to copy the mdb file to a backup folder.
Let me know if you need more info on creating the vba code.

 

[Unami]

Thank you for the fileCopy suggestion. I guess there isn't a simple macro to do it. I used the fileCopy in a module but got error. Is that just for strings?

 

[natatbh]

Here is how it works,

FileCopy "C:\Folder1\myDB.mdb", "C:\BackUp Folder\backUp.mdb"

cut and paste this line then replace the the first argument with the path to you original mdb file and the second argument with the path you want to backup to.

let me know if you need any more help

 

[Unami]

Thank you for your patience. Can I attach that function to a button property or just put it in a module?

 

[natatbh]

You seem to be pretty much new to msaccess, hang in there and you'll learn fast.

To attach the function to a button create a command button on your form. Right click on the button and open its properties.
Go to the event tab and select [Event Procedure] from the drop down in the 'on click' event then click on the ... button on the right. This will automatically open the vba code editor and position you cursor in the sub. Paste the line of code in the sub and save. Now open your form and try clicking the button, it should do the desired.

 

[Unami]

Thank you for all your help. It worked great!

 

[shallman]

I have also been watching this post and trying this. I get an error: Microsoft Visual basic, run-time error '70'. Permission Denied.

It seems that since the VB FileCopy command is being run from inside the 'mdb' it is trying to back up, it get's this error.

If I make the source file in FileCopy command another file in the same directory, it works fine.

I can also copy the database file using Windows Explorer while the database file is open without incident.

Why may this not be working for me?

Thanks,
shallman

 

[jrbarnett]

Shallman,

This code demonstrates how to copy the file. It is based on code written in response to another thread recently, but it shows how to copy a file (even the currently open database) from within VBA.

Public Sub CopyCurrentFile()
' Purpose: Function to copy the current database in Access 
' Author: John Barnett, handle jrbarnett
' Date: 30th July 2003
' In response to thread702-615057 Notes: It is not possible to copy the current file over via VBA because it is open so the copy fails.

' I should also point out that this code makes no checks to ensure that it won't overwrite any existing file, or that an existing file is read only or has operating system or network security enabled on it which would cause the copy to fail.

Dim strName As String
Dim strNewname As String
Dim strCommandline As String

strName = CurrentDb.Name ' current (source) path
strNewname = CurrentDb.Name ' new name
strNewname = Left$(strNewname, Len(strNewname) - 4) ' take the dot and 3 char extension off.
strNewname = strNewname & "2.mdb" ' so filename.mdb becomes filename2.mdb
strCommandline = Environ("COMSPEC") & " /C COPY " & strName & " " & strNewname ' execute a command to copy the file
' command will read something like:
' C:\WINNT\System32\CMD.EXE /C COPY F:\John\Database.MDB F:\John\Database2.MDB
Shell strCommandline ' run it
End Sub

John

 

[spiralmind]

one idea:

'//////////////////////////////////////////////////////////////////////////////////////

Sub subBackupDB()

' Declare win32 file system:

Dim fs
Set fs = CreateObject("Scripting.FileSystemObject&quot

' Assign backup folder:

Dim i As Integer
Dim bolFind As Boolean


Do Until bolFind = True

If Left(Right(CurrentDb.Name, i), 1) = "\" Then bolFind = True

i = i + 1

Loop

Dim strFile As String
strFile = Right(CurrentDb.Name, i - 2)

Dim strDest As String
strDest = "C:\Documents and Settings\n0111546\" ' example backup folder

Dim strInto As String
strInto = strDest & "(Backup " & Format(Date, "mm-dd-yyyy&quot & &quot " & strFile

' If the filename is unique in the destination folder:

If fs.FileExists(strInto) = False Then

' Move the file:

fs.CopyFile CurrentDb.Name, strInto

End If

End Sub

'//////////////////////////////////////////////////////////////////////////////////////

this copies the active database to a directory you specify and puts a date stamp on its name. you can put it in your database's open or close events, or you can call it from a button on a form or an action necessitating backup. as it is set up now it will make only one backup per day, keeping the first backup of each day and preventing any others after that. you can change this behavior by playing with the "if" statements and inserting a delete statement to clear an older version out of the way for a newer one made the same day (if you want to keep the last session rather than the first, look up the "Kill" statement in vba help, or check the "overwrite" option of the "copyfile" command). this way your db can be backed up automaticly (or close to) on every day it is opened.

- note: this function could also be called by a button on a form in the same database or in another that you use as a switchboard for this sort of common administrative tasks. if you don't want the whole date-stamp thing added to your filename you can easily cut it out and find some other mechanism for indexing multiple backups if you wish to keep more than one of them around. if this doesn't work from within the database, try the second database version where the second application closes and copys the first.