Daily Database Backup

[dmh4ab]

This is puzzling me.
In my application I wish to backup an access database the first time a user opens a form each day.
At random times this will work more than once a day. (My quess is that something is going astray when I check the file dates). Also, it takes a long time on some user's PC and a short time on others. (Have no clue why this would be!)
(Operating system is windows 2000 - Access 97 - using VB 6 language)

Here is the code:

When the program is first loaded:

DateTime = Format(FileDateTime("C:\RoutingsBackup\eRoutings_backup.mdb"), "mm/dd/yyyy hh:mm AMPM") 
FileDate = CDate(Left(DateTime, 10)) 

'capture current date 
SystemDate = CDate(Format(Now, "mm/dd/yyyy")) 
'if they are the same, no need to backup again 
If FileDate = SystemDate Then 
    backup = False 
'database needs to be backed up locally 
Else 
    backup = True 
    'Here go to Access to retrieve latest snapshot of data and store the returned Process ID. 
    mlngProcessHandle = Shell("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE S:\Group\Eng\eRoutings\eRoutings_database.mdb", vbHide) 

    'Based on the original Process handle, we generate a new Process handle 
    '(but not a new process) that has terminate rights. 
    mlngProcessHandle = OpenProcess(PROCESS_TERMINATE, 0, mlngProcessHandle) 
End If

When the form is opened that would trigger the backup:

'only backup if this boolean option is set to true (first time opening today) 
If backup = True Then 
    'copy database for backup purposes 
    RepairEntry.Show 
     
    With fileop 
        .hWnd = 0 
        .wFunc = fo_copy 
        .pFrom = "S:\Group\Eng\eRoutings\eRoutings_database.mdb" & vbNullChar & vbNullChar 
        .pTo = "C:\RoutingsBackup\eRoutings_backup.mdb" 
        .lpszProgressTitle = "Please wait, backing up eRoutings database locally....." 
        .fFlags = FOF_SIMPLEPROGRESS Or FOF_NOCONFIRMATION 

    End With 
    lret = SHFileOP(fileop) 

    'We Call Terminate Process with the process handle that has terminate rights. 
    'If the API return a 1, then the function completed without error. 
    TerminateProcess mlngProcessHandle, lngExitCode 
End If

declarations (in a main module)

Public conn As Connection 
Public rsDetails As ADODB.Recordset 
Public NewRec As Boolean 
Public RevRec As Boolean 
Public InProcess As Boolean 
Public Update As Boolean 
Public backup As Boolean 

'variables and structures required to backup the database 
Public lret As Long 
Public fileop As SHFILEOPSTRUCT 
Public Type SHFILEOPSTRUCT 
    hWnd As Long 
    wFunc As FO_Functions 
    pFrom As String 
    pTo As String 
    fFlags As FOF_Flags 
    fAnyOperationsAborted As Long 
    hNameMappings As Long 
    lpszProgressTitle As String 'only used if FOF_SIMPLEPROGRESS 
End Type 
Public Enum FO_Functions 
    FO_MOVE = &H1 
    fo_copy = &H2 
    FO_DELETE = &H3 
    FO_RENAME = &H4 
End Enum 
Public Enum FOF_Flags 
    FOF_MULTIDESTFILES = &H1 
    FOF_CONFIRMMOUSE = &H2 
    FOF_SILENT = &H4 
    FOF_RENAMEONCOLLISION = &H8 
    FOF_NOCONFIRMATION = &H10 
    FOF_WANTMAPPINGHANDLE = &H20 
    FOF_ALLOWUNDO = &H40 
    FOF_FILESONLY = &H80 
    FOF_SIMPLEPROGRESS = &H100 
    FOF_NOCONFIRMMKDIR = &H200 
    FOF_NOERRORUI = &H400 
    FOF_NOCOPYSECURITYATTRIBS = &H800 
    FOF_NORECURSION = &H1000 
    FOF_NO_CONNECTED_ELEMENTS = &H2000 
    FOF_WANTNUKEWARNING = &H4000 
End Enum 
Public Declare Function SHFileOperation Lib _ 
    "shell32.dll" Alias "SHFileOperationA" _ 
    (lpFileOp As Any) As Long 
Public Declare Sub CopyMemory Lib "kernel32" _ 
    Alias "RtlMoveMemory" (hpvDest As Any, hpvSource _ 
    As Any, ByVal cbCopy As Long) 
'used to autocomplete combobox 
Public Const CB_FINDSTRING = &H14C 
Public Const CB_ERR = (-1) 
Declare Function SendMessage Lib "user32" Alias _ 
                                 "SendMessageA" _ 
                                 (ByVal hWnd As Long, _ 
                                  ByVal wMsg As Long, _ 
                                  ByVal wParam As Long, _ 
                                  lParam As Any) As Long 
'used to shell and terminate to other functions 
'OpenHandle Desired Access Constants. 
Public Const PROCESS_TERMINATE = 1 
'Required APIs to terminate other functions. 
Public Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long 
Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long 
'External Process Handle. 
Public mlngProcessHandle As Long 
Public lngExitCode As Long

Any help would be greatly appreciated. So far as I can tell checking the file dates should work!

Thanks,
dmh4ab

 

[petevick]

Are all your users PC system dates reset to a central server date when they log on?, using something like NET TIME \\servername in a script file, could there sys dates be out of sync with each other?. I can't think what else it might be.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk