Cannot get tables or database to save off without getting errors, none of the DoCmd's work for me

[Moosee]

Private Sub cmdRolloverData_Click()
   
   Dim Answer As String
   Dim TargetPath As String
   Dim ArchiveDBName As String
   Dim aApp As Access.Application
   Dim dbPathAndName As String
   Dim db As DAO.Database
   
   On Error GoTo RolloverError
   
   Answer = MsgBox("Are you sure you want to archive/rollover the 'Current/Prior Year' rate data?", vbQuestion + vbYesNo, "???")
   
   If Answer = vbYes Then
   
      DoCmd.Hourglass True
      Application.Echo False
   
      TargetPath = Application.CurrentProject.Path
      ArchiveDBName = "CRASuspense_Archive_" & Format(Now, "yyyymmdd") & ".accdb"
      dbPathAndName = TargetPath & "\" & ArchiveDBName
      Set aApp = New Access.Application
      aApp.NewCurrentDatabase dbPathAndName, acNewDatabaseFormatUserDefault
      aApp.Quit
      DoCmd.SetWarnings False
      DoCmd.CopyObject dbPathAndName, "tblCurrentYear", acTable, "tblCurrentYear"
      DoCmd.CopyObject dbPathAndName, "tblPriorYear", acTable, "tblPriorYear"
'      DoCmd.CopyDatabaseFile DatabaseFileName:=dbPathAndName, OverwriteExistingFile:=True, DisconnectAllUsers:=False
      DoCmd.SetWarnings True
      
      Set db = CurrentDb
      ' Finish cleaning up the Prior and Current Year tables
      CurrentDb.Execute "qry_Rollover_Delete_PriorYear"
      CurrentDb.Execute "qry_Rollover_Append_CurrentYear_to_PriorYear"
      CurrentDb.Execute "qry_Rollover_Delete_CurrentYear"
      CurrentDb.Execute "qry_Rollover_Update_tblFiscalYear"
      CurrentDb.Execute "qry_Rollover_Update_tblPeriod"
      
      DoCmd.Hourglass False
      Application.Echo True
      
      MsgBox "The Archive/Rollover is finished.  The Archive database is " & dbPathAndName
      
   End If
   Exit Sub
RolloverError:
 '  MsgBox "There was a error while trying to Archive/Rollover."
   MsgBox Err.Description
   Application.Echo True
   DoCmd.Hourglass False


End Sub

[Welshgasman]

Works for me?
Would have to ask why set db = CurrentDb and then use Currentdb.Execute ?

[Moosee]

Just a mistake that needs to be fixed. Thank you.

[Micron]

So it's all good now? vbYes data type is Long Integer but you're assigning it to a string variable. I think I can see why it works, but it's a dicey way to code it. Better to make the variable an integer or a long.

[Moosee]

Ok, thanks, I will fix, but that is not what is giving me the problem.