08-08-2007, 08:42 PM
How I use oRS.delete ? How to refer record to be deleted ?
How I understand that there is no relate record in other table, to the record to be deleted [to can actually delete this record] ?
08-10-2007, 08:26 PM
To use the ADO delete method you just open your recordset and then call the delete method. For example
sSQL = "SELECT * FROM myTable WHERE Color='blue'"
oRs.Open sSQL, myConnection,1,2
Do While Not oRs.EOF
'delete all records one at a time
Set oRs = Nothing
As to how to determine if a related record in another table exists, the best way is to do a query to determine if there is a related record. You may be trying to delete orphaned records and want to make sure there is no parent record (the record in the table that holds the primary key) There are a number of ways to determine if the record has no match in the other table obviusly the first is to do an out right query looking for the record. If eof is returned instead of some records you know that there is no matching record. You can also try an outer join in a query to see which records are returned with the information missing from the recordset.
To prevent orpaned records, if this is a SQL Server database make sure that you have your constraints set up so that when a record in the table that holds the primary key is deleted you delete all of the records in the related tables by using ON DELETE CASCADE.
example, this code prevents orphaned UserInfo records because when the Related record in the Users table is deleted it cascades to the UserInfo table and also deletes the record with the foreign key to the parent
ALTER TABLE UserInfo ADD
CONSTRAINT FK_USERINFO Foreign Key (UserID)
ON DELETE CASCADE
I do have a question, Why not just use a SQL delete statement to delete the records?