Microsoft KB Archive/247766

= ACC2000: How to Programmatically Delete Orphaned Records =

Article ID: 247766

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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:  Start Microsoft Access, and then open the sample database Northwind.mdb. 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. 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. Using AutoReport: Tabular, create a report named rptTestCustomers, based on the OldCustomers table.  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  Open the rptTestCustomers report, and then observe the first four records shown. Close the report.</li> Open the ValidCustomers table, delete the first four records, and then close the table.</li> 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.</li></ol>

<div class="references_section">