Access.2007.VBA.Bibl..

ChDir strDBPath

On Error Resume Next

If there already is a saved worksheet with this name, delete it:

Kill strSaveName

On Error GoTo ErrorHandler

wkb.SaveAs FileName:=strSaveName, _

FileFormat:=xlWorkbookDefault

wkb.Close

rst.Close

Put up a success message, listing the name and path of the new worksheet:

strTitle = “Workbook created”

strPrompt = “Archive workbook ‘“ & strSheetTitle & “‘“ _

& vbCrLf & “created in “ & strDBPath

MsgBox strPrompt, vbOKOnly + vbInformation, strTitle

Delete the archived records, processing the “many” table first, because you can’t delete a record in
the “one” table if there are linked records in the “many” table:

DoCmd.SetWarnings False

strSQL = “DELETE tblOrderDetails.*, “ _

& “tblOrders.ShippedDate “ _

& “FROM tblOrderDetails INNER JOIN qryArchive “ _

& “ON tblOrderDetails.OrderID = qryArchive.OrderID;”

Debug.Print “SQL string: “ & strSQL

DoCmd.RunSQL strSQL

strSQL = “DELETE tblOrders.* FROM tblOrders WHERE “ _

& “[ShippedDate] Between #” & dteStart & “# And #” _

& dteEnd & “#;”

Debug.Print “SQL string: “ & strSQL

DoCmd.RunSQL strSQL

Put up a message listing the cleared records:

strTitle = “Records cleared”

strPrompt = “Archived records from “ _

& Format(dteStart, “d-mmm-yyyy”) _

& “ to “ & Format(dteEnd, “d-mmm-yyyy”) _

& “ cleared from tables”

MsgBox strPrompt, vbOKOnly + vbInformation, strTitle

ErrorHandlerExit:

Exit Sub

Analyzing Data with Excel 3