ACC2000: How to Programmatically Delete Orphaned Records (247766)



The information in this article applies to:

  • Microsoft Access 2000

This article was previously published under Q247766
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article describes how to programmatically delete orphaned records from a dependent table based on data in a parent table.

NOTE: Orphaned records are records that no longer contain useful information and cannot be looked at. For example, if you have records that contain information about customers, but there is no way to identify the customers, the data is said to be orphaned.

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.

REFERENCES

For more information about the Delete method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type new methods (alphabetical list) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about OpenRecordset method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type openrecordset method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbinfo KB247766