Working with ADO recordsets and Queries?

[JonnyCab]

Hi,
Im trying to delete unwanted data from a table using ADO recordsets and the 'Find' and 'Delete' queries.
.rscmdTemp contains project cost records.
.rscmdProjStatus contains a list of completed projects.
I am trying to delete any records from .rscmdTemp where the cost record relates to a project that has not been completed.

Im using the 'Find' method (Part 1 of code example) to see if each record is for a completed project. I assume that if .rscmdProjStatus.EOF after the 'Find' search then no matching record was found...is this correct?

Also how can I delete all records in a recordset where the contract code matches the current contract code ie in part 2 of code example.


With DataEnvironment1
.Connection2.Open
.rscmdTemp.Open
.rscmdProjStatus.Open
.rscmdTemp.MoveFirst

PART 1
Do While Not .rscmdTemp.EOF
.rscmdProjStatus.Find ("WO = '" & .rscmdTemp!Contract & "'")

PART 2
If .rscmdProjStatus.EOF Then
strSQL = "DELETE FROM .rscmdTemp WHERE .rscmdTemp!Contract = .rscmdTemp!Contract"
Set Temp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)
Set Temp = Nothing
End If
.rscmdTemp.Close
.rscmdProjStatus.Close
.Connection2.Close
End With


Any help would be much appreciated.... Cheers

[JonnyCab]

Hi again,

Im just replying to my question because no one else will...
by the way I am still stuck on the same problem.

Any Help?

[JonnyCab]

I've got things working after about 4 hours of trying different things. Code is below. If you have a better way of doing this please let me know.

With DataEnvironment1
.Connection2.Open
.rscmdTemp.Open
.rscmdProjStatus.Open
.rscmdTemp.MoveFirst

Do While Not .rscmdTemp.EOF
.rscmdProjStatus.MoveFirst
.rscmdProjStatus.Find ("WO = '" & .rscmdTemp!Contract & "'")
If .rscmdProjStatus.EOF Then
strSQL = "DELETE * FROM tblTemp WHERE Contract = '" & .rscmdTemp!Contract & "'"
Set Temp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)
Set Temp = Nothing
.rscmdTemp.Requery
Else
.rscmdTemp.MoveNext
End If
Loop