ACC2000: How to Programmatically Delete Orphaned Records (247766)

MORE INFORMATION

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

In the following example you first find orphaned records in a copy of the Customers table of the Northwind.mdb sample database that no longer have valid CustomerIDs, and then delete them.

To find the orphaned records, match common key fields in the parent table and the dependent table, and then create a table with only the unmatched IDs. You can concatenate the values in that table into one string by using the OpenRecordset method. You can then delete the orphaned records in the dependent table with the Delete method by using the string that you created.

To find and delete orphaned records in the example, follow these steps:

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. Using a Make-Table query, create a new table named ValidCustomers, which contains all of the values in all of the fields of the existing Customers table.
   
   This is the parent table.
3. Using a Make-Table query, create a second new table, named OldCustomers, which contains all of the values in all of the fields of the existing Customers table.
   
   This is the dependent table.
4. Using AutoReport: Tabular, create a report named rptTestCustomers, based on the OldCustomers table.
5. Create a form named TestOpenReport, with one command button, and then place the following event procedure in the OnClick property of the button:

   Private Sub Command0_Click()
   Dim Db As DAO.Database
   Dim DataTbl As DAO.Recordset
   Dim QD As DAO.QueryDef
   Dim CritData As String
   
   Set Db = CurrentDb()
   
   'Delete existing data table and queries. Trap for error and continue if object does not exist.
   On Error Resume Next
   Db.TableDefs.Delete ("UnmatchedIDs")
   Db.QueryDefs.Delete ("CreateData")
   Db.QueryDefs.Delete ("DeleteData")
   On Error GoTo 0
   
   'Create new UnmatchedIDs data source table by creating find unmatched query.
   Set QD = Db.CreateQueryDef("CreateData", "SELECT DISTINCTROW OldCustomers.CustomerID INTO UnmatchedIDs FROM OldCustomers LEFT JOIN ValidCustomers ON OldCustomers.CustomerID = ValidCustomers.CustomerID WHERE (((ValidCustomers.CustomerID) Is Null));")
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "CreateData"
   
   'Create string CritData from list of customer IDs in UnmatchedIDs table.
   Set DataTbl = Db.OpenRecordset("UnmatchedIDs")
   CritData = ""
   With DataTbl
       Do Until .EOF
           CritData = CritData & "'" & ![CustomerID] & "',"
           .MoveNext
       Loop
       .Close
   End With
   
   'Remove last comma from criteria string
   Dim CritLen As Integer
   
   CritLen = Len(CritData)
   CritData = Mid(CritData, 1, CritLen - 1)
   
   'Delete missing records from Customers table.
   Set QD = Db.CreateQueryDef("DeleteData", "DELETE OldCustomers.* FROM OldCustomers Where [CustomerID] In(" & CritData & ");")
   DoCmd.OpenQuery "DeleteData" 'Deletes unmatched records from OldCustomers
   
   'Open Report in Print Preview
   DoCmd.OpenReport "rptTestCustomers", acPreview, "", ""
   End Sub
   					

6. Open the rptTestCustomers report, and then observe the first four records shown. Close the report.
7. Open the ValidCustomers table, delete the first four records, and then close the table.
8. Open the TestOpenReport form, and then click the command button.
   
   Note that the report opens, and the first four records of the OldCustomers table are no longer shown.