Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: How I use oRS.delete ?
08-08-2007, 08:42 PM #1
- Join Date
- Dec 2005
- Thanked 0 Times in 0 Posts
How I use oRS.delete ?
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 #2
- Join Date
- Dec 2002
- Arlington, Texas USA
- Thanked 8 Times in 8 Posts
To use the ADO delete method you just open your recordset and then call the delete method. For example
Code: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 oRs.Delete oRs.MoveNext Loop oRs.Close 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
Code:ALTER TABLE UserInfo ADD CONSTRAINT FK_USERINFO Foreign Key (UserID) REFERENCES Users(UserID) ON DELETE CASCADE
I do have a question, Why not just use a SQL delete statement to delete the records?