As I mentioned in another post, the way the "Auto-Deletion" of aged records was implement is a tad "heavy", in the sense that the query is invoked at each time an entry is logged. I kind of find it a bit much.
The following code snippet, which is currently what I'm using on my site, determine when the last set of records were deleted, and initiate the process only if necessary.
I might have been a bit too cautious about that process, but I know how picky Access is, so I don't want to jeopardise the data intergrity.
Here's the code :
=====================================================================
If intDeleteAfterDays > 0 Then
blnOkToDelete=False
strDeletionStatus=Application("mttdelsts")
' Application variable not itialized. IIS/server was recycled. Do the deletion
If (strDeletionStatus="") Then
'
' We'll set the variable and read it back to ensure integrity.
'
Application.Lock
strDeletionStatus=FormatDateTime(Now(),0) & "|R|" & Session.SessionID
Application("mttdelsts") = strDeletionStatus
Application.UnLock
actDeletionStatus=Application("mttdelsts")
blnOkToDelete=(actDeletionStatus=strDeletionStatus)
Else
'
' Get the variable content. Invoke the deletion only if appropriate
'
aryDeletionStatus=Split(strDeletionStatus,"|")
If (FormatDateTime(Now(),2) <> FormatDateTime(aryDeletionStatus(0),2)) Then
'
' We'll set the variable and read it back to ensure integrity.
'
Application.Lock
strDeletionStatus=FormatDateTime(Now(),0) & "|R|" & Session.SessionID
Application("mttdelsts") = strDeletionStatus
Application.UnLock
actDeletionStatus=Application("mttdelsts")
blnOkToDelete=(actDeletionStatus=strDeletionStatus)
End If
End If
If (blnOkToDelete=True) Then
strSql = strInstance & "PageLog " &_
"WHERE pl_datetime < " & FormatDatabaseDate(Dateadd("d", (0 - intDeleteAfterDays), Now()))
strDeleteCnt=GetRecCnt(strSql)
objConn.Execute("DELETE FROM " & strSql)
Application.Lock
strDeletionStatus=FormatDateTime(Now(),0) & "|C|Count : " & strDeleteCnt
Application("mttdelsts") = strDeletionStatus
Application.UnLock
End If
End If
=====================================================================
Globally, this is how it works.
The Application variable ("mttdelsts") is retrieved.
- If it is blank, either IIS or the server was recycled, or this is the first time the code evaluates the variable. Under these circumstances, the variable is set using the current system timestamp, the letter "R" (Running) and finally the SessionID. The session ID is mainly for debugging purposes, easying the determination of the page the user was on when the deletion process took place.
The deletion process takes place.
Upon completion of the deletion, the Application variable is updated with a "C" (Completed) indicator, along with a count of the records that were actually deleted.
If the variable was previously initialized, and was done today, the deletion process is simply skipped, no further action is taken.
We could go the "extra mile" and store the actual Deletion status information so that it could be retrieved if need be, and also be used as an history.
A combination of the Application variable and a storage area to keep track of the statuses would probably be the best way to go, ensuring there would be no invokation of the deletion process if the previous attempt failed, thus allowing investigation prior to re-instating the automated process.
Finally, I do read back the application variable, mainly for double-check, just in case the variable didn't get updated as expected. This might not be necessary, but I prefer being extra cautious during the implementation phase. Once everything is proved to be stable and reliable, then I can re-address the appropriate sections of code.
By the way, here is the GetRecCnt code: (Adapted from another already existing routine)
=============================================================
Private Function GetRecCnt(usrQuery)
Dim strSql, objRs, intRecords
strSql="SELECT COUNT(pl_ipaddress) FROM " & usrQuery
Set objRs=objConn.Execute(strSql)
If Not objRs.Eof Then
intRecords=objRs(0)
Else
intRecords=0
End If
objRs.Close
Set objRs=Nothing
GetRecCnt=intRecords
End Function
=============================================================
I added some extra infomation on the summary page of the tool, so that this variable along with other "kind of usefull" pieces of information are available thus allowing the admin to have a kind of "in control" feeling.
Happy tracking! :)