Worked in Access 2000?

[keving]

I am having problems with some ADO code that creates or updates a record in a database table. The code worked perfectly under Access 2000 but I get a duplicate record message under Access XP, when the record already exists. I assume ADO rules have been tightened up and I am doing something wrong? Following is a section of code that is giving me problems. I have not provided the definitions.

' Set the connection to the local database for reading and updating the Winning teams table
Set cnn = CurrentProject.Connection

' Load the work table
For intSub = 1 To 21
stSql = "SELECT * FROM [Work Table]"
stSql = stSql & " Where [Team]= " & intSub & ";"

rstWorktable.Open stSql, cnn, adOpenDynamic, adLockOptimistic

' If the record exists replace it, otherwise, insert a record.
If (rstWorktable.EOF) Then
rstWorktable.AddNew
End If
rstWorktable![Team] = intSub
rstWorktable![Points] = TeamPoints(intSub, 1)
rstWorktable![points5] = TeamPoints(intSub, 2)
rstWorktable![Points6] = TeamPoints(intSub, 3)
rstWorktable.Update

' Close the recordset

rstWorktable.Close

Next intSub

The error occurs at line rstWorktable.Update. Let me know what I am doing incorrectly.

Kevin ....

 

[dcx693]

I think that to test for an empty recordset in ADO, you need to check whether you are simultaneously at the beginning and at the end of the recordset using:

If rstWorktable.BOF And rstWorktable.EOF Then