New in ADO method and Sql Server

[neoklis]

Hi guys

I will try to explain (bad English) an error I am dealing
I connect to an Sql 2000 Server with Access. I used to work with DAO method and I realize that is to slow. So I started converting my code to ADO method and at this time I am working on a problem.

When I try to edit an opened recordset (form) through code, an error appears:

“Operation is not allowed when the object is open”

Any help would be appreciate

Thnx

 

[neoklis]

I resolved the previous error message but i am stil dealing with a new one:

"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

Any help?

 

[neoklis]

In Sql Server there was a navchar(255) field in a table. I have import that table from Access and the specific field was memo type. The memo field in Access was converted to navchar in SQL and there was some trouble with null entries. I left it as it was and in default value i add:

N' '


Yooohoooo

 

[Dennisk]

it sounds as though you are not applying the correct cursor/lock but I may be wrong. Post your code and highlight the line in error.

 

[neoklis]

it sounds as though you are not applying the correct cursor/lock but I may be wrong. Post your code and highlight the line in error.

thanks dennisk for your reply. This is my code. As i previous said the problem is solved but i would really like to take a look to my code. Any suggestions will be pleasantly accepted.
________________________________________________________________
Public Function EditRecSpin(Id, DMin, DMax, SMin, SMax, Typ, Rangekey)
Static Rec As New ADODB.Recordset

If IsNull(Rangekey) Or IsEmpty(Rangekey) Or Rangekey = "" Then
Exit Function
End If

Rec.Open "SpinnerTime", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Rec.RecordCount > 0 Then
Rec.MoveFirst
Do While Not Rec.EOF
If Rec("ID") = Id Then
Rec("DMin") = DMin
Rec("DMax") = DMax
Rec("SMin") = SMin
Rec("SMax") = SMax
Rec("Type") = Typ
Rec("RangeKey") = Rangekey
Rec.Update ' this is the point that crash
Exit Do
End If
Rec.MoveNext
Loop
End If

Rec.Close

End Function

 

[Dennisk]

If ID is a primary key ithen try using an sql statement in the open method so you restrict the number of rows returned to one.

Rec.Open "SELECT * FROM SpinnerTime WHERE [ID] =" & iD, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

IF Not REC.EOF Then
'' Updates here
endif

 

[neoklis]

Dennisk thank you very much for your help. The way you suggest me to open the recordset is the proper way. Thank you again..

PS: My name in Greek is (Διονύσης) Dionisis and in English is Dennis like your.

 

[Dennisk]

the god of love and wine I think! or in the case of the british - boozing and shagging

 

[neoklis]

..and in the case of Orthodox St Dionisios