Douglas,

I think my last post:

I now get a Access error ("The database has been placed in a state by user
'Admin' on machine 'Computer' that prevents it from being opened or locked.
(3734) (Error in) Transaction rolled back and not tables updated.)

is what I was trying to get with the code to identify errors so I could add
code to ignore some errors and maybe as a result, I get the Run-Time error
'3034'? Example of code I would use to ignore errors below:

If Err.Number = 3022 Then
   If dbs.QueryDefs(strQuery).Type = dbQAppend Then
        dbs.Execute strQuery
       Resume Next
  End If

   If Err.Number = 3734 Then
   If dbs.QueryDefs(strQuery).Type = dbQAppend Then
        dbs.Execute strQuery
       Resume Next
  End If

But now when I run the code with these changes, it does not run or give an
error message. The following is the code with the changes: Note that I have
commented out some code to save it but not run as I am just trying to
identify error codes at this time.

Private Sub Command8_Click()

  Dim dbs As DAO.Database
   Dim wrk As DAO.Workspace
   Dim strSQL As String, strQuery As String, strMessage As String

   strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
           Format(Date, "\#mm/dd/yyyy\#")
   
   On Error GoTo Err_Handler

   If Time() > #6:30:00 AM# Then

       If DLookup("LastTimerDate", "tblTimerDate") < Date Then
       
           Set wrk = DAO.DBEngine.Workspaces(0)
           Set dbs = CurrentDb
           
           
           ' begin transaction
           wrk.BeginTrans
       
           ' 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
           ' Update Housing Units from Patient table to Patients table
           strQuery = "4Update Housing from Patient to Patients"
           dbs.Execute strQuery ', dbFailOnError
           ' Deletes records from RX1 that are "GONE" over 1 year
           strQuery = "5RX1 Delete >1 years Query"
           dbs.Execute strQuery ', dbFailOnError
       
          ' Update tblTimerDate table
          strQuery = "embedded SQL to update tblTimerDate"
          dbs.Execute strSQL ', dbFailOnError
       
           ' no error so commit transaction
           wrk.CommitTrans
   
       End If
   End If
   
Exit_Here:
   Exit Sub
   
Err_Handler:
   
   
   
   
   If Err.Number = 3022 Then
   If dbs.QueryDefs(strQuery).Type = dbQAppend Then
        dbs.Execute strQuery
       Resume Next
  End If

   If Err.Number = 3734 Then
   If dbs.QueryDefs(strQuery).Type = dbQAppend Then
        dbs.Execute strQuery
       Resume Next
  End If

Else
  strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine & _
     "(Error in " & strQuery & ")" & _
     vbNewLine & vbNewLine & "Transaction rolled back and no tables updated."
   MsgBox strMessage, vbExclamation, "Error"
   wrk.Rollback
End If

   ' The below code is
   'If Err.Number = 3022 And dbs.QueryDefs(strQuery).Type = dbQAppend Then
       'dbs.Execute strQuery
       'Resume Next
   'Else
       'strMessage = Error & " (" & Err.Number & ")" & vbNewLine &
vbNewLine & _
           '"(Error in " & strQuery & ")" & _
           'vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."
       'MsgBox strMessage, vbExclamation, "Error"
   
       'wrk.Rollback
   End If
   Resume Exit_Here

End Sub

What do you think?

Thanks,
Dennis