Deleting multiple records from the same table

jscrogg · June 6th, 2006, 11:17 AM

I'm trying to delete multiple records in a table with the following code:

'Delete the record from the 'tblUGDate' table
    rstUGDate.CursorType = adOpenDynamic
    rstUGDate.LockType = adLockOptimistic
    rstUGDate.Open "SELECT * FROM tblUGDate WHERE [ID] = '" & txbID & "'", CurConn, , , adCmdText
    With rstUGDate
        .Delete
    End With
    rstUGDate.Close

The code is good for finding one record with the ID, it then deletes it and quits.

I want to delete all of the records that have the same ID.

Thanks
Jeremy

woodyz · June 6th, 2006, 03:54 PM

You can use a delete statement, or you can loop through all records returned from your select statement, deleting each as you go.

Method 1 - delete statement

Code:

CurrConn.Execute("Delete From tblUGDate Where [ID] = '" & txbID & "'")

Method 2 - Loop through recordset

Code:

do while not rstUGDate.eof
  rstUGDate.Delete
  rstUGDate.MoveNext
loop

Have fun

Woody Z http://www.learntoprogramnow.com

jscrogg · June 7th, 2006, 01:58 PM

Thanks a lot

arasu · June 12th, 2006, 05:30 AM

1. You can use delete Query
2. You can use movenext statements with in the with statements

Regards
Adalarasu. S
Xyka Software Pvt ltd, Bangalore