Backup Access database automatically

[dollygoh]

Hi,

I need to backup my Access database on every Friday night. Does anyone has any idea how to go about doing it? Do we actually need to keep our application active all the time inorder to achieve this requirement ? Thanks !

[Hack]

What do you want to back it up to?

A network drive?
A diskette?
A CD?
Tape?

VBs FileCopy would work, but only if the database is closed.

On the other hand, the CopyFile method of the FileSystemObject will copy a file, even if it is open.

[aidan]

We discovered our automatic backup wasn't backing up databases in use, (and most were in use at the backup time). We now use a housekeeping program to make a copy of the database, so that this, which is not in use, gets backed up. The mirror copy also provides a convenient short term backup.

[Hack]

Did you develop the "housekeeping program" that you mentioned aidan?

[dollygoh]

I will backup the database in the network drive.

Hack, do you have a simple example on how to use the CopyFile method? So if some users are updating data at the same time when you backup the database, will there be any problem ? How do you deal with such situation ?

aidan, how do you create a housekeeping program and how you detect whether the database is in used ?

Actually, my main concern is that, I can I do it automatically?

thank in advance.

[Hack]

'Set a reference to the FileSystemObject (Microsoft Scripting Runtime)
Option Explicit
 
Private BkUpDb As New FileSystemObject
 
Private Sub Form_Load()
BkUpDb.CopyFile "c:\program files\qcap\qcap.Mdb", "W:\systems\hack\qcap.Mdb", True
End Sub

[aidan]

What hack has written is pretty much what I use. It checks to see if a folder exists with today's date on it, if it doesn't then it makes it and copies the files. It doesn' t matter if they are in use, although obviously any changes which haven't been updated yet won't be saved.

The function can be triggered manually or with a timer

Here is the code

Public Function MDbackupdatabases() As Long
    Dim DBname$(5)
    Dim oldfolder$
    Dim newfolder$
    Dim t&
    Dim fso As Object 'As New scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    oldfolder = "T:\"
    newfolder = "T:\DBbackups " & format(Date, "ddMMyy")
    retval = MDcreateFolder(newfolder)
    If retval Then 'folder did not previously exist
        DBname(0) = "Contacts2000.mdb"
        DBname(1) = "Project2000.mdb"
        DBname(2) = "newindexes2000.mdb"
        For t = 0 To 2 'cycle through all files
            fso.copyfile oldfolder & DBname(t), newfolder & DBname(t), False  'false means will not overwrite
        Next t 'cycle through all files
        MDbackupdatabases = 1
    End If 'folder did not previously exist 

[aidan]

Oops - I formatted to PHP not VB code

I am planning to improve it by making it carry out a test access of the database to check that it hasn't been corrupted before the file is copied, but I haven't got round to doing this yet.

[texas]

I'm not sure if this is exactly what you are looking for, but I use it to make copy and compact the DB at the same time.

http://support.microsoft.com/default...;en-us;Q209979

[dollygoh]

Thanks texas, but I am using Access 97 and Visual Basic 6. The article you provided is for Access 2000.

[Hack]

Even though the article if for Access 2000, I wouldn't be surprized to discover you could use the fundamentals of it for Access 97.

Give it shot and see what happens.

[texas]

I took out the timer event code and set the DB to autoexec the code when the DB opened. i.e. OnOpen event of the form. That way VB can call it or in my case I used the Scheduled Tasks wizard to run it.

[marleyuk]

What hack has written is pretty much what I use. It checks to see if a folder exists with today's date on it, if it doesn't then it makes it and copies the files. It doesn' t matter if they are in use, although obviously any changes which haven't been updated yet won't be saved.

The function can be triggered manually or with a timer

Here is the code

Public Function MDbackupdatabases() As Long
    Dim DBname$(5)
    Dim oldfolder$
    Dim newfolder$
    Dim t&
    Dim fso As Object 'As New scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    oldfolder = "T:\"
    newfolder = "T:\DBbackups " & format(Date, "ddMMyy")
    retval = MDcreateFolder(newfolder)
    If retval Then 'folder did not previously exist
        DBname(0) = "Contacts2000.mdb"
        DBname(1) = "Project2000.mdb"
        DBname(2) = "newindexes2000.mdb"
        For t = 0 To 2 'cycle through all files
            fso.copyfile oldfolder & DBname(t), newfolder & DBname(t), False  'false means will not overwrite
        Next t 'cycle through all files
        MDbackupdatabases = 1
    End If 'folder did not previously exist 

Hi, above Aiden has added a script that backups even when the database is in use. I am looking for this exact thing for accesss. As Aiden has written this in PHP, does anyone ahve something similar for access please?

Thanks,
Ian.

[techgnome]

That's not PHP... it's VB... he jsut used the PHP syntax highlighter by mistake... but it's VB code all the way through.

-tg

[estrohat]

Hello guys

Public Function MDbackupdatabases() As Long 
    Dim DBname$(5) 
    Dim oldfolder$ 
    Dim newfolder$ 
    Dim t& 
    Dim fso As Object 'As New scripting.FileSystemObject 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    oldfolder = "T:\" 
    newfolder = "T:\DBbackups " & format(Date, "ddMMyy") 
    retval = MDcreateFolder(newfolder) 
    If retval Then 'folder did not previously exist 
        DBname(0) = "Contacts2000.mdb" 
        DBname(1) = "Project2000.mdb" 
        DBname(2) = "newindexes2000.mdb" 
        For t = 0 To 2 'cycle through all files 
            fso.copyfile oldfolder & DBname(t), newfolder & DBname(t), False  'false means will not overwrite
        Next t 'cycle through all files 
        MDbackupdatabases = 1 
    End If 'folder did not previously exist

I'm getting an error in this lines of codes

newfolder = "T:\DBbackups " & format(Date, "ddMMyy")

error is Type expected