Solved

msaccess sqlserver and cannot update recordset due to lock violation

Posted on 2008-06-25
4
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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