maintaining the backend (1 Viewer)

[Foe]

I've spent the last two days searching for answer to this one and all I've really found is enough ideas to come up with the code below.

My environment is a split database with a few different front ends connecting to the same back end. Only one of the front ends will be used for data manipulation. All others will be report based (pulling useful information from the existing data).

All of the report based front ends will be used by day staff with typical office hours. The data entry front end will typically be single user and in use 24/7.

With that, I feel fairly confident that overnight is the best time to perform back end maintenance since only one user is affected and I can give them some control over exactly when the back end maintenance occurs.

To that end I've come up with the following outline:

Dim Response 
Response = msgbox("Run scheduled maintenance?" & vbcrlf & vbcrlf & _
		  "Selecting yes will momentarily disable the database." & vbcrlf & _
		  "Select no if you have any unsaved work.", vbyesno, "Scheduled Maintenance Required")
If Format(TimeValue(Now()), "hh:mm") = #01:00# then
    If Response = vbyes then
	***KICK OTHER USERS OUT***
	***CLOSE FRMHOME***
	***DISCONNECT FROM BACKEND***
	***BACKUP BACKEND***
	***REPAIR AND COMPACT BACKEND***
	***CONNECT TO BACKEND***
	***OPEN FRMHOME***
    else: ***WAIT 5 MINUTES AND ASK AGAIN***
    end if
End if

Obviously, I still need to write the code to do each of the ***ALL CAPS*** lines. The above was typed out using Notepad just to capture my thought process.

My question to you is about my logic; Is it sound? Does this seem like it would work? Is there a better way to do it?

Thank for taking a look!

 

[Poppa Smurf]

Only one of the front ends will be used for data manipulation. All others will be report based (pulling useful information from the existing data).

For reporting purposes have you considered having a database on each users workstation that will handle the data manipulation for reports? This database will be linked to the users front end on their workstation.

In your current environment is there a possibility that the users may generate the same report for a different date range?

 

[jmaty23]

I have a similar setup as you described above. I have a split access database with around 5 front end users that all generally work on first shift 7-3:30am.

What I did for this situation is build into my main navigation form code to quit the front-end database at 5am if it was left open by the user. When the first user in the morning opens the front-end, it checks to see if the back-end database is locked, if not, then it performs the back end DB backup and then compacts and repairs before the front-end opens and locks the back-end database.

I have this built into the front-end start up because there could be some times during the day when the front end is launched and the back-end is unlocked, this would provide additional means to Compact and Repair the backend database.

Here is the code I used in the front-end to quit access at 5am. It runs in the Timer subroutine of a form that is always open.

If Hour(Now()) = 5 Then
DoCmd.Quit
End If

This code is run in the splash form when the database is first launched. It checks if the back-end is locked by calling CheckBELock, if not locked, it calls CompactBackend function.

Private Sub Form_Load()
On Error GoTo ErrorHandler

If CheckBELock = False Then
Call CompactBackend
End If

DoCmd.OpenForm "frmxsplash"
DoCmd.Close acForm, "frmdefault"

ProcedureExit:
Exit Sub

ErrorHandler:

MsgBox "Error" & ": " & Err.Number & vbCrLf & "Description: " _
& Err.Description & vbCrLf & "This error has been logged for review. Contact Maintenance Planning at Ext. 8550 if the issue persists.", vbExclamation
Call ErrorLog(Err.Number, Err.Description, Form.Name)
Resume ProcedureExit

End Sub

Then in a Module, I have this code:

Option Compare Database
Option Explicit

Public Function CheckBELock() As Boolean
On Error GoTo ErrorHandler

Dim strPath As String

strPath = "\\"DATABASE BASE\FIS_be.ldb"
' This checks whether the record locking file exists (backend is open if it exist
If Len(Dir(strPath)) = 0 Then
' doesn't exist
' MsgBox "False"
CheckBELock = False
Else
' MsgBox "True"
CheckBELock = True
End If

ProcedureExit:
Exit Function

ErrorHandler:

MsgBox "Error" & ": " & Err.Number & vbCrLf & "Description: " _
& Err.Description & vbCrLf & "This error has been logged for review. Contact Maintenance Planning at Ext. 8550 if the issue persists.", vbExclamation
Call ErrorLog(Err.Number, Err.Description, "")
Resume ProcedureExit

End Function

Function CompactBackend()
On Error GoTo ErrorHandler

Dim strPath As String
Dim strFileName As String
'strPath is the path to the folder containing your backend
'strFileName is the backend's full filename ie "my_Backend.accdb"

strPath = "\\BACKEND FOLDER DIRECTORY\"
strFileName = "fis_be.mdb"

On Error GoTo Err_CompactBackend

Dim mNewPath As String
Dim mPath As String

'Names the compacted DB with _Compacted suffix
mNewPath = strPath & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "_Compacted.mdb"
mPath = strPath & "\" & strFileName

'Delete old _Compacted file if exist
If Len(Dir(mNewPath)) Then
Kill mNewPath
End If
'MsgBox "Compacting database"
Application.CompactRepair LogFile:=True, sourcefile:=mPath, DestinationFile:=mNewPath
' If this is successful then we rename the source file with the compacted one
'First backup the old uncompacted source file
Dim tempPath, tempPath2 As String
tempPath2 = mPath
'Adds a _Backup suffix
tempPath = strPath & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "_Backup.mdb"
'Make a backup copy of the Source file
FileCopy mPath, tempPath
'Kill the original file
Kill mPath
'Rename the new compacted file to original filename
Name mNewPath As tempPath2
CompactBackend = True
'MsgBox "Done Compacting database"

ProcedureExit:
Exit Function

ErrorHandler:

MsgBox "Error" & ": " & Err.Number & vbCrLf & "Description: " _
& Err.Description & vbCrLf & "This error has been logged for review. Contact Maintenance Planning at Ext. 8550 if the issue persists.", vbExclamation
Call ErrorLog(Err.Number, Err.Description, "")
Resume ProcedureExit

End Function

 

[Cronk]

If Hour(Now()) = 5 Then
 DoCmd.Quit
End If

This is a bit "brutal" isn't it? Especially, half way through a record entry. In any case, every time the Timer fired between 5 am and 6 am, the db would be closed down, even after logging back in after the Compact/Repair.

I've not had need to do this sort of process, but I would at 4.30am, say, give a prompt that system maintenance will occur at 5 am unless the user wants to do it beforehand. Keep giving 5 minute prompts until 4.59 am when the user is told the system will mandatorily close at 5 am.

As part of the backup process, store the date of BE maintenance, so it's not run twice on the same day.

 

[Foe]

Here's how I did it

This code runs on a hidden form on a 5 minutes timer:

Private Sub Form_Timer()
Dim LocalTime, StartWindow, EndWindow As Date
LocalTime = TimeValue(Now())
StartWindow = TimeValue(Me.lblStartWindow.Caption)
EndWindow = TimeValue(Me.lblEndWindow.Caption)
If LocalTime > StartWindow And LocalTime < EndWindow Then
    'scheduled maintenance prompt
    Dim Response
    Response = MsgBox("Run scheduled maintenance?" & vbCrLf & vbCrLf & _
                      "Selecting YES will momentarily disable the database." & vbCrLf & _
                      "Select NO if you have unsaved work.", vbYesNo, "Scheduled Maintenance Required")
    If Response = vbYes Then
        'set logout flag - logout flag is monitored by other front ends to prevent back end connections
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryLogoutTrue"
        DoCmd.SetWarnings True
        'close frmHome - this disconnects front end from back end
        DoCmd.Close acForm, "frmHome"
        'display maintenance message
        Me.Visible = True
        'run the module to backup, compact and repair
        CompactDB ("tblHotword")
        'hide maintenance message
        Me.Visible = False
        'reset logout flag
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryLogoutFalse"
        DoCmd.SetWarnings True
        'reopen frmHome
        DoCmd.OpenForm "frmHome"
        'reset times to default
        Me.lblStartWindow.Caption = "1:00:00 AM"
        Me.lblEndWindow.Caption = "1:05:00 AM"
    Else 'move maintenance window to the right 5 minutes
        Dim strStart, strEnd As String
        strStart = DateAdd("n", 5, StartWindow)
        strEnd = DateAdd("n", 5, EndWindow)
        Me.lblStartWindow.Caption = strStart
        Me.lblEndWindow.Caption = strEnd
    End If
End If
End Sub

How it works is that between 0100 and 0105 (StartWindow and EndWindow, respectively) the user is asked if it's okay to run maintenance. Asking them gives them the option to say NO if they're currently working on a record. When they say NO, the StartWindow and EndWindow move to the right and become 0105 and 0110. It will continue to move right by 5 minutes each time they say NO. When they do eventually say YES, a logout flag is set which will disconnect any other front ends that got left open over night,the maintenance runs and then StartWindow and EndWindow get reset to their default values. Those values are stored as the captions of two hidden labels on the same form.

The function actually performing the maintenance is discussed in this thread.

Poppa Smurf, I'm not sure what you mean by a database on each desktop. They will each have their own front end, but your post seems to suggest their own back end. I will only be using a single back end that all other front ends are linked to.
And, yes, it is possible that the same report could be run on varied date ranges. Most of the reports will be snapshot views of the current data to answer the question of the moment. I don't anticipate them being printed or archived in any other way.

 

[Poppa Smurf]

Foe

I'm not sure what you mean by a database on each desktop. They will each have their own front end, but your post seems to suggest their own back end

.
From your previous posting the users are using the same data for the reports and there is a possibility of more than one user using the same report for different date ranges.

Regarding my comment above, if only queries are used for the record source for the reports then there will be no problems. But,if you are manipulating the data for a date range using a table as the record source for the reports then there maybe a possibility that the data for one user could .be overwritten by the data of another user.

 

[jmaty23]

Cronk, this works fine in my particular situation. There is never a chance that a user that changes data will be logged in at 5am. I uploaded it so that other people can use variations of the code to make it work for their DB.