Timer Form

[Don]

I have some code that Updates, Appends, Deletes and Makes Tables. The problem
I am having is if something goes wrong with the process like someone
accessing the database from the front end, the codes rolls back except for
tables that are deleted. Once that happens the tables must be restored before
the update can take place (this requires me). What I am wondering, is there a
way to do the following code and have the deleted tables restored if the code
errors out and rolls back? The employees rely on two tables "BUSUpdateTbl"
and "PillLine". Of course these two tables should be updated each day but if
they are not updated, there presents will allow medical service to continue
(several employees to keep working) until I arrive to work.

Private Sub Form_Timer()

' Dim tells the database about a variable such as dbs, wrk and
' strSQL and the type of data the variable it will store.
Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strSQL As String, strQuery As String, strMessage As String

' strSQL represents the data that follows the = sign each time strSQL
' is included in this code.
strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
Format(Date, "\#mm/dd/yyyy\#")


On Error GoTo Err_Handler

If Time() > #5:20:00 AM# Then

If DLookup("LastTimerDate", "tblTimerDate") < Date Then

Set dbs = CurrentDb
Set wrk = DAO.DBEngine.Workspaces(0)

' begin transaction
wrk.BeginTrans

' Select BUSUpdateTbl
DoCmd.SelectObject acTable, "BUSUpdateTbl", True
' Delete BUSUpdateTbl
DoCmd.DeleteObject , ""

' Select PillLine table
DoCmd.SelectObject acTable, "PillLine", True
' Delete PillLine table

DoCmd.DeleteObject , ""


' Appends Rx table to RX1 table "Adds new RX's to RX1 table"
strQuery = "1Append Rx to RX1 Query"
dbs.Execute strQuery ', dbFailOnError
' Append Patient table to Patients table "Adds new records"
strQuery = "2Append Patient to PatientsQuery"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from Patients that are "GONE" over 1 year
strQuery = "3Delete >1 years From Patients qry"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from RX1 that are "GONE" over 1 year
strQuery = "4RX1 Delete >1 years Query"
dbs.Execute strQuery ', dbFailOnError

' Imports and transferes BUS.txt to table BUS if BUS.txt is
' present in specified directory.
If (Len(Dir("D:\Medical\BUS.txt"))) Then
DoCmd.TransferText acImportFixed, "BUS Import Specification", "BUS",
"D:\Medical\BUS.txt", False, ""
End If

'Makes Table BusUpdateTbl for updating housing in Patients table
strQuery = "5MakeBUSUpdateTblqry"
dbs.Execute strQuery ', dbFailOnError
'Updates/Changes Housing to GONE for Patients GONE in Patients
table
strQuery = "6UpdateGoneImqry"
dbs.Execute strQuery ', dbFailOnError
'Updates housing and DOB in Patients table
strQuery = "7UpdateBusHousingQry"
dbs.Execute strQuery ', dbFailOnError
' Makes PillLine Table
strQuery = "8MakePillLineTable"
dbs.Execute strQuery ', dbFailOnError

' Update tblTimerDate table
strQuery = "embedded SQL to update tblTimerDate"
dbs.Execute strSQL, dbFailOnError

' no error so commit transaction This means that all of
' the queries above will be created. If the code stops,
' before commiting the transaction, the tables that are
' to be created will be created but only in the memory
' and if you keep running the code to debug the database
' you will get an error reporting that the tables exist
' even when you check the tables area in database window
' and you do not see them. The tables will be created once
' the commit transaction completes.

wrk.CommitTrans

DoCmd.Quit

End If
End If

Exit_Here:
Set dbs = Nothing
Exit Sub

Err_Handler:

strMessage = Error & vbNewLine & vbNewLine & _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."
MsgBox strMessage, vbExclamation, "Error"
' roll back transaction
wrk.Rollback
Resume Exit_Here




End Sub

What is the best way to achieve this end?

Thanks,

Dennis