Backup (1 Viewer)

[Exodus]

Ok I've been reading on backing up Dbs here. I have a db FE and BE I need to do back ups currently I backing up my FE with the autocompact db found in the sample forum but can't use it to back up my BE when it's open.

bascialy I'm trying to backup the BE daily but how do I do it if some is in it?

 

[ghudson]

This is a popular thread with the answer you seek...
http://www.access-programmers.co.uk/forums/showthread.php?t=57863

 

[Exodus]

I have actualy been looking at this thread but the problem I have run across from reading is data corruption if there ar users in the db when you copy the db.

 

[ghudson]

I have never seen or experienced any data corruption from copying the db with the code I posted in that thread. You can always kick the users out of the db if want. There are plenty of threads discussing how to kick all users out of db. I have probably chimmed in on a few tof them.

 

[Exodus]

Thanks that is what I was looking for.

 

[Exodus]

Code Check

I have adapted the code a bit from some other examples. I am not very good a coding so I'm hoping if there is any problems someone can point them out for me. The code is working though.

Public Function AutoBackup() As Boolean

Dim fso As FileSystemObject
Dim sSourceFile As String
Dim sSourcePath As String
Dim sBackupPath As String
Dim sBackupFile As String
Dim strFileName As String
Dim sBackupFolder As String
Dim sFilePart As String
Dim sFileExtension As String
Dim lngRecord     As Long
    
      
    
    
With CurrentDb.OpenRecordset("tblCompactTable", conDbOpenDynaset)

.MoveLast
.MoveFirst
        
            '   Initialize the statusbar meter.
            SysCmd acSysCmdInitMeter, "Backing Up Files...", .RecordCount
        
            '   Start looping.
            For lngRecord = 1 To .RecordCount
            

sSourcePath = !FileName
sFilePart = ParseFileName(sSourcePath, 2)
            
sBackupPath = !NewFileName
sBackupFile = sFilePart & "_" & Format(Date, "dd-mm-yyyy") & "-" & Format(Time, "hh-mm-ssAMPM") & ".mdb"

Set fso = New FileSystemObject
fso.CopyFile sSourcePath, sBackupPath & sBackupFile, True
Set fso = Nothing


'   Update the statusbar meter, and move to next record.
                SysCmd acSysCmdUpdateMeter, lngRecord
                .MoveNext
            Next lngRecord
SysCmd acSysCmdRemoveMeter
        
 End With

End Function

 

[ghudson]

Interesting twist to my backup routine. I had to make a few changes to the code but it now works and you might like what I did.

For those following this thread... You need to create a table named "tblBackUpFiles" and two fields named "FilePathName" and "BackUpPath". The values in the "FilePathName" field need to be the location and name of the file to be backed up like "X:\MyFiles\Database.mdb" and the values in the "BackUpPath" field need to be just the backup path [including the ending backslash /] like "C:\BackUps\"

Public Function BackupDatabases()
    
'You must set a reference to the Microsoft Scripting Runtime
    
    Dim fso As FileSystemObject
    Dim sSourceFile As String
    Dim sBackupPath As String
    Dim sBackupFile As String
    Dim sFileName As String
    Dim lngRecord As Long
    
    With CurrentDb.OpenRecordset("tblBackUpFiles", dbOpenDynaset)
    
        .MoveLast
        .MoveFirst
    
        SysCmd acSysCmdInitMeter, "Backing Up Files...", .RecordCount
    
        For lngRecord = 1 To .RecordCount
    
        sSourceFile = !FilePathName
        sFileName = ParseFileName(sSourceFile)
    
        sBackupPath = !BackUpPath
        sBackupFile = sFileName & "_" & Format(Date, "yyyy-mm-dd") & "_" & Format(Time, "hhnnss") & ".mdb"
    
        Set fso = New FileSystemObject
        fso.CopyFile sSourceFile, sBackupPath & sBackupFile, True
        Set fso = Nothing
    
        SysCmd acSysCmdUpdateMeter, lngRecord
            .MoveNext
        Next lngRecord
    
        SysCmd acSysCmdRemoveMeter
    
    End With
    
    MsgBox "Backups finished"
    
End Function

Public Function ParseFileName(sFile As String) As String
On Error GoTo Err_ParseFileName

    Dim sPath As String
    
    sPath = sFile
    
    Do While Right$(sPath, 1) <> "\"
    sPath = Left$(sPath, Len(sPath) - 1)
    Loop
    
    ParseFileName = Mid$(sFile, Len(sPath) + 1)
 
Exit_ParseFileName:
    Exit Function

Err_ParseFileName:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ParseFileName
 
End Function

 

[Exodus]

Thanks Ghudson,

Looks a lot cleaner for than what I had. I'm still working out the bugs but I'm looking at having froms to select dbs and paths which I got this whole idea from another utlility db here called auto compact. The problem I was having with it was it wouldn't copy an open db so I started for alternatives. I will post my new creation after it is working for those who are interested.

 

[ghudson]

You need to ensure that you have lots of error trapping to handle the situations when the file can not be found, directory can not be found, file already exists, can not be read, etc. to bullet-proof your backup function. The users have a habit of messing with a file [path\name] after it's location info has been stored so you have to also account [trap] for that.

You might already have a browser function but I will offer my Browse [Find a directory or file] sample for it will allow you to copy [import] the functions I have created to allow a user to select a file and select a directory.

 

[Exodus]

Path Problem

Ok it's been a while but I'm picking this one up again. I'm getting an invalid call or procedure when I don't use a drive letter for the path of the source file. Like this \\Rov_nt2\opsuprt\BallotQty.mdb. Can anyone help me on this.

 

[Exodus]

So any solutions yet?

 

[ghudson]

For starters you are not testing if the file [directory] exists. Just because it was there last month does not mean that it will still be there the next time they run the backup function. You need to trap for all possible errors a user can create when searching for files and also trying to copy/delete/move/etc.

I can not get your code to work with using a Drive letter\path\file or using the UNC instead. I do not have time to disect your work but you should search around for their are other examples on how to do something like what you are attempting.

This link shows you how to get the UNC out of a drive letter.
Sample Code to Return the UNC Path of a Network Drive

I searched around [a little bit] but could not find the opposite which is what you are looking for.

I just noticed that the properties of your db mentions the user ChrisO. He posts some very good examples and code and I think you need to go back to the sample you started with and look at what he is doing for I recall seeing a sample of his that compacts a listing of db's.

 

[Exodus]

Thanks ghudson

True I haven't tested for the directory or the sorce file yet which is on my list to do. I wanted to get the UNC problem fixed first. I'm not having any problems with the drive letter.