Automating Archiving Records in Microsoft Access, using Append and Delete Queries:

[Fred's]

Hello All,

I"ve copy the vba code which is from the following link:
http://www.databasedev.co.uk/automate_ms_access_archive.html"

Then, I've been trying to change the code for my use, based on my table
fields,
but, then, error message appear" two many line continuation"

Therefore, I've been trying to write the code as follow.

Those someone can help me. Does my code is correct
I'm not sure if Iwrote the code correctly.

Thanking for your help!


Here is the code:

Private Sub cmdArchiveData_Click()
'Run Archive - Append and Delete

Dim strSQLAppend As String
Dim strSQLDelete As String
Dim errLoop As Error
Dim dteExpiry As Date
dteExpiry = DateAdd("yyyy", -2, Date)

' Define two SQL statements for action queries.
strSQLAppend = "INSERT INTO nctblExpired " & _
"( scarID, scarNumber, supplierName, retekNumber,
problemDate, season, division, orderNumber, country, style, color1,
color2, color3, color4, problemCode, problemCode2, problemCode3,
problemCode4, problemCode5, problemCode6, problemDescription, impact,
correctiveAction, preventiveAction, reworkManhours, hourlyRates,
laborCost, disposition, reworkInstruction, sppCoordinator, rootCause,
supplierResponse, responseCode, responseDate, respCode, reviewedBy,
reviewDate, followUpHistory, status, generateScar, sentToSuppliers,
closeDate, merchType, verificationDate, verifiedBy, expired ) " & _
"SELECT nctbl.scarID " & _
"nctbl.scarNumber, nctbl.supplierName, nctbl.retekNumber,
nctbl.problemDate, nctbl.season, nctbl.division, nctbl.orderNumber,
nctbl.country, " & _
"nctbl.style, nctbl.color1, nctbl.color2, nctbl.color3, nctbl.color4,
nctbl.problemCode, nctbl.problemCode2, nctbl.problemCode3,
nctbl.problemCode4, " & _
"nctbl.problemCode5, nctbl.problemCode6, nctbl.problemDescription,
nctbl.impact, nctbl.correctiveAction, nctbl.preventiveAction, " & _
"nctbl.reworkManhours, nctbl.hourlyRates, nctbl.laborCost,
nctbl.disposition, nctbl.reworkInstruction, nctbl.sppCoordinator,
nctbl.rootCause, " &_
"nctbl.supplierResponse, nctbl.responseCode, nctbl.responseDate,
nctbl.respCode, nctbl.reviewedBy , nctbl.reviewDate,
nctbl.followUpHistory, nctbl.Status, " & _
"nctbl.generateScar, nctbl.sentToSuppliers, nctbl.closeDate,
nctbl.merchType, nctbl.verificationDate, nctbl.verifiedBy,
nctbl.expired " & _
"FROM nctbl " & _
"WHERE nctbl.problemDate <= #" & dteExpiry & "#;"


strSQLDelete = "DELETE nctbl.scarID, " & _
"nctbl.scarNumber, nctbl.supplierName,
nctbl.retekNumber, nctbl.problemDate, nctbl.season, nctbl.division,
nctbl.orderNumber, nctbl.country, nctbl.style, nctbl.color1,
nctbl.color2, nctbl.color3, nctbl.color4, nctbl.problemCode,
nctbl.problemCode2, nctbl.problemCode3, nctbl.problemCode4,
nctbl.problemCode5, nctbl.problemCode6, nctbl.problemDescription,
nctbl.impact, nctbl.correctiveAction, nctbl.preventiveAction,
nctbl.reworkManhours, nctbl.hourlyRates, nctbl.laborCost,
nctbl.disposition, nctbl.reworkInstruction, nctbl.sppCoordinator,
nctbl.rootCause, nctbl.supplierResponse, nctbl.responseCode,
nctbl.responseDate, nctbl.respCode, nctbl.reviewedBy, nctbl.reviewDate,
nctbl.followUpHistory, nctbl.status, nctbl.generateScar,
nctbl.sentToSuppliers, nctbl.closeDate, nctbl.merchType,
nctbl.verificationDate, nctbl.verifiedBy, nctbl.expired " & _
"FROM nctbl " & _
"WHERE nctbl.problemDate <= #" & dteExpiry & "#;"

' Run action query to restore data. Trap for errors,
' checking the Errors collection if necessary.
On Error GoTo Err_Execute
CurrentDb.Execute strSQLAppend, dbFailOnError
CurrentDb.Execute strSQLDelete, dbFailOnError
On Error GoTo 0

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

 

[Douglas J. Steele]

You cannot have more than 10 line continuation characters in a single
statement.

Where you currently have

strSQLAppend = "INSERT INTO nctblExpired " & _
"...." & _
"...." & _
"...." & _

etc., change it to

strSQLAppend = "INSERT INTO nctblExpired " & _
"...." & _
"...."
strSQLAppend = strSQLAppend & "...." & _
"...." & _
"...."