Solved

msaccess sqlserver and cannot update recordset due to lock violation

Posted on 2008-06-25
4
400 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:sydneyguy
  • 2
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
dosth earned 500 total points
ID: 21874227
myrecsetattenexist.Open querystringatten, myconn, adOpenKeyset, adLockPessimistic

try

myrecsetattenexist.Open querystringatten, myconn, adOpenKeyset, adLockOptimistic
0
 

Author Comment

by:sydneyguy
ID: 21874253
have tried that as well and still did not work another suggestion? but we must be close
0
 

Author Closing Comment

by:sydneyguy
ID: 31470879
Thanks so much for your help
0
 
LVL 15

Expert Comment

by:dosth
ID: 22435304
thanks
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question