msaccess sqlserver and cannot update recordset due to lock violation

hI have a msaccess project hooked up to sqlserver and I am trying to update a record set
did some research and foind this page from microsoft.
http://support.microsoft.com/kb/289675
which is correct as my error throws up the error **** Current Recordset does not support updating. This may be a limitation of the provider, or of the selected *****  now I have been and made the approp changes to the system as below but it keeps throwing up the lock problem, any ideas what to do next

Private Sub Addclass_Click()
    'ACCESS THE DATA FOR THE TABLE ATTENDENCE
On Error GoTo Err_Addclass_Click
    ' CHECK THAT A DATE EXITS AND CLASS AND COURSE EXIST
   
            Dim myconn As New ADODB.Connection
            Dim myrecsetattenexist As New ADODB.Recordset
            Dim StudentID As String ' extract this from the mainform page
            myconn.Open CurrentProject.Connection
            Dim querystringatten As String
          querystringatten = "Select * from Attendance where StudentID = '" &                                                                 
                               Forms!MainForm!Student.Form!StudentID & "' AND DateOfAttend = '" & datrval & "'"
                               
             myrecsetattenexist.Open querystringatten, myconn, adOpenKeyset, adLockPessimistic '
              If myrecsetattenexist.EOF = True Then
                                     
                      myrecset.MoveFirst
                      myrecset.AddNew
                       'MsgBox (ActionDate)
                        myrecset.Fields("StudentID") = Forms!MainForm!Student.Form!StudentID
                       myrecset.Fields("DateOfAttend") = datrval
                       myrecset.Fields("NoOfHrsExpect") = Forms!MainForm!Student.Form!hoursperweek
                       myrecset.Fields("NoOfHrsActual") = 0
                        myrecset.Fields("StudCourseID") =  
                                       Forms![MainForm]![Student].Form![frmsubStudentInfo].Form![CourseID]
                        'MsgBox ("adding")
                         ' myrecset.Fields("ClassID") = "14"
                         myrecset.Update
                                           
                 End If
                               
Exit_Addclass_Click:
    Exit Sub

Err_Addclass_Click:
    MsgBox (Err.Description)
    MsgBox ("Check the dates and Class ID")
    Resume 'Exit_Addclass_Click


End Sub
sydneyguyAsked:
Who is Participating?
 
dosthCommented:
myrecsetattenexist.Open querystringatten, myconn, adOpenKeyset, adLockPessimistic

try

myrecsetattenexist.Open querystringatten, myconn, adOpenKeyset, adLockOptimistic
0
 
sydneyguyAuthor Commented:
have tried that as well and still did not work another suggestion? but we must be close
0
 
sydneyguyAuthor Commented:
Thanks so much for your help
0
 
dosthCommented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.