Archive Code - SQL Syntax Query

[CarolW]

Hello Everybody.

I was wondering if anyone could please help me with a SQL syntax issue that I am currently experiencing problems with.

I have a requirement to archive data over 2 years old, the data from which extracted from two tables on which I there is a one-to-many relationship. The PK from Table A is a National Insurance No which acts as the FK in Table B.

I managed to source some code (included below), which does a great job for one table (Table A), but I am unsure as to how I need to modify it in order to collect and archive its associated data in Table B. I have searched various forums for a solution but to no avail….

The fields in Table B are strNINo, strTrgProvider, strTrgActivity, strSector, strTutor and TrgCost
……………………………………………………………………………………………………..

What I Currently Have Is This:

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 tblExpiredStudents " & _
"( strNINo, strFirstName, strLastName, strAddress1, " & _
"strAddress2, strCity, strCounty, strPostCode, strTelephone, " & _
"hypE-mailAddress, dtmDOB, dtmEnrolled, strCourseID ) " & _
"SELECT tblStudentInformation.strNINo, " & _
"tblStudentInformation.strFirstName, " & _
"tblStudentInformation.strLastName, " & _
"tblStudentInformation.strAddress1, " & _
"tblStudentInformation.strAddress2, " & _
"tblStudentInformation.strCity, " & _
"tblStudentInformation.strCounty, " & _
"tblStudentInformation.strPostCode, " & _
"tblStudentInformation.strTelephone, " & _
"tblStudentInformation.E-mailAddress, " & _
"tblStudentInformation.dtmDOB, " & _
"tblStudentInformation.dtmEnrolled, " & _
"tblStudentInformation.strCourseID " & _
"FROM tblStudentInformation " & _
"WHERE tblStudentInformation.dtmEnrolled <= #" & dteExpiry & "#;"


strSQLDelete = "DELETE tblStudentInformation.strNINo, " & _
"tblStudentInformation.strFirstName, " & _
"tblStudentInformation.strLastName, " & _
"tblStudentInformation.strAddress1, " & _
"tblStudentInformation.strAddress2, " & _
"tblStudentInformation.strCity, " & _
"tblStudentInformation.strCounty, " & _
"tblStudentInformation.strPostCode, " & _
"tblStudentInformation.strTelephone, " & _
"tblStudentInformation.hypE-mailAddress, " & _
"tblStudentInformation.dtmDOB, " & _
"tblStudentInformation.dtmEnrolled, " & _
"tblStudentInformation.strCourseID " & _
"FROM tblStudentInformation " & _
"WHERE tblStudentInformation.dtmEnrolled <= #" & 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

Resume Next

End Sub

Any help or guidance to resolve my problem would be extremely well appreciated - it would save me tearing from my hair out??? You've all been so very helpful to me in the past, and so I once again call upon your kind nature and expertise to help me out of a potentially sticky situation....

Best Regards

CarolW

 

[zyxwvu44]

why not cascade delete on the relationship?

 

[pbaldy]

Generally:

SELECT whatever FROM table2 WHERE IDField IN(SELECT IDField FROM Table1 WHERE DateField meets your criteria)

By the way, you don't need to list all the fields in the DELETE. This is enough:

DELETE * FROM...

 

[CarolW]

Hello zyxwvu44 and Paul,
Thanks for taking the time out to reply................
Your advice about the 'cascade delete on the relationship' is something I already have in place, and if I am correct should automatically delete stuff from Table B when I delete records from Table A?

Paul - with regards to what you advised, how would I incorporate what you have said in respect to the code I have already supplied. My knowledge of SQL is very basic and as a consequence I am very unsure as to where I should place it with respect to the text supplied. Could you possibly show me an example based on what I have already posted in order that I could understand it better.

Apologies for the delay in getting back to you on this subject.

Kind Regards

Carol

 

[pbaldy]

You would execute it between the 2 existing bits. You would want to append records to the "main" table, then the "child" table, then delete. If you're unsure about the SQL itself, the best way to learn is to do. First play with the subquery (inside the parentheses) in the query designer until it returns the correct records. Then incorporate that SQL into the main query until it returns the correct fields and records. Then convert that into an append query similar to your existing append query. But for the subquery it's the same as what you're already doing. The query/subquery is basically saying "give me the records from the child table that have the same ID as these records from the main table".

To answer your other question, yes; if you have cascade delete set up, when you delete a record from the "main" table its related record(s) in the child table should be deleted automatically.

 

[CarolW]

Paul,
Thanks for your help....

I'll have a go at what you suggested - with your guidance I'll hopefully achieve my aim.

Regards

Carol