multiple delete query with junction table??

[pablavo]

Hi there.

I'm trying to archive records by using append and delete queries that span accross 12 tables.

the VBA will execute them all. However, I have a junction table near the end; It will be the Junction table, then a parent then a child table. I can get the junction table keys deleted but after that I can't get the Parent and the child table records deleted.

Does anyone know how to do this. the parent table can't delete any records because I've deleted the junction table keys first but if I delete the parent table keys access obviously wont let me because it's leaving orphan records.

I'm sure there's a way to do this. hope someone can help me.

Thanks.

 

[Jurjen]

delete query solution

Hi,

Suppose you have 3 tables, of which 1 is an intersection (or junction) table:

OBJE (Objects):
obje_id (long) = primary key
obje_name (text)

COLR (Colors):
colr_id (long) = primary key
colr_name (text)

OBCO (Object-Colors):
obje_id (long) = primary key
colr_id (long) = primary key

Their relationship is as follows:

OBJE <-- OBCO --> COLR

Suppose you want to delete all objects from your database that are exclusively black, and also the color black itself. You could run the following delete queries:

DELETE OBCO.*
FROM OBCO INNER JOIN COLR ON OBCO.colr_id=COLR.colr_id
WHERE COLR.colr_name = "Black";

This query deletes all links between the color black and objects that have the color black

DELETE COLR.*
FROM COLR WHERE COLR.colr_name = "Black";

This query deletes the color black

DELETE OBJE.*
FROM OBJE WHERE OBJE.obje_id NOT IN (SELECT obje_id FROM OBCO)

This query deletes all object that have no color. If the objects should always have at least one color, then this is alright, because then only the objects that were exclusively black before running the first query will meet these criteria. But if the database also allows for colorless objects, then you will delete those also.

There is another solution, that gives you more control over what happens: use DAO (Data Access Objects). To enable this you should add a reference to Microsoft 3.6 DAO Object Library. You can do this in VB, menu Tools:References. Make sure that its priority is as high as possible (otherwise Recordset might refer to an ADO library)

Then execute the following code:

Sub del_black()
    Dim db As Database
    Dim rst_obje As Recordset, rst_colr As Recordset, rst_obco As Recordset
    
    Set db = CurrentDb()
    Set rst_obje = db.OpenRecordset("OBJE", dbOpenDynaset)
    Set rst_colr = db.OpenRecordset("COLR", dbOpenDynaset)
    Set rst_obco = db.OpenRecordset("OBCO", dbOpenDynaset)
    
    rst_colr.FindFirst "colr_name = 'Black'"
    If Not rst_colr.NoMatch Then
        rst_obco.FindFirst "colr_id = " & rst_colr!colr_id
        While Not rst_obco.NoMatch
            rst_obje.FindFirst "obje_id = " & rst_obco!obje_id
            rst_obco.Delete
            rst_obje.Delete
            rst_obco.FindNext "colr_id = " & rst_colr!colr_id
        Wend
        rst_colr.Delete
    End If
    Set rst_obje = Nothing
    Set rst_colr = Nothing
    Set rst_obco = Nothing
End Sub

This code assumes that all tables have at least 1 record and therefore performs no BOF (Beginning Of File) check. To make a solid procedure that would also be required.

 

[pablavo]

Thanks Jurjen! you've obviously took some thought in this.

I ended up making a new field (archive Yes/no) within the parnet table on the other side of the junction table. and created code so that when a user ticked the archive check box a dialog box would popup asking the user if this was a project to be archived. If the user agreed they would be directed to the form ( that has the parent table) record source and this code would automatilcally be ticked. therefor I could make a Delete query just with the parent table record and the child with disregard if they where part of a junction table.

I'm going to try your code to see if it works and let you know how I get on.

In the mean time, here's the code I used for the technique above:
'in the befor update within the "archive" chkbox

If Me.chkArchive = -1 Then

strmsg = "Warning, if you Archive this project it can only be viewed " & vbCrLf & _
"within Archive reports. " & Chr(10)
strmsg = strmsg & "Click Yes to Archive or No to Discard changes."

intResponse = MsgBox(strmsg, vbQuestion + vbYesNo, "Save Record?")

MsgBox "The Cofinance form will now open. Please make sure the archive field " & vbCrLf & _
"within the form is ticked also. "
' the cofinance form will open and will be ticked.

' this will execute when the OpenArgs is "yes" (tick box within the main form is ticked) and be a seperate process. clumsy as it might be :/

If Me.OpenArgs = "Yes" Then
' Opens the tab control "cofinance" if the Archive check box is ticked within _
' frmProjectMain, and sets the "chkArchive" check box within the CoFinance form check Bcx to "yes" (-1) Me.Page146.SetFocus
Me.sfrmCofinanceAmountApproved!chkArchiveProcess = -1

End If