Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    21

    Red face Unanswered: Access Lock Violations

    am working on a project that involves access and sql server
    i conncted my access project to my data base by using odbc connection.
    now i can read data from my tables, but when i'm trying to insert data or delete it i get an error:

    "can't delete 1 record(s) in the delete query due to key violations and 0 record(s) due to lock violations"
    (when i tried to delete a raw)

    now if i will run the query while the table is open it will be executed

    i don't really understand the problem
    is it cous i have some tables that are connected to each other? do i need to open connection to all the tables? and how can i do that?

    please help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    First verify that all tables on the server have a primary key and that the definition of this primary key is corectly imported into Access.

    Also, if you open recordsets or use the .execute method against onject in the database on the server, do not forget to use the dbSeeChanges optional parameter, otherwise your call will fail. Ex.
    Code:
    Set rst = CurrentDB.OpenRecordset("ServerTable", dbOpenDynaset, dbSeechanges)
    or:
    Code:
    strSQL = "DELETE * FROM ServerTable WHERE ..."
    CurrentDB.Execute strSQL, dbSeeChanges
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Posts
    21
    hei
    the definition of the primary key are imported corectly
    all this code is kinda new to me
    i write diffrentlly:
    Dim strSQL As String
    Dim rst As New ADODB.Recordset
    rst.Open "dbo_tblPlayer", CurrentProject.Connection
    rst.MoveFirst
    Do While (Not rst.EOF)
    If (rst.Fields("playerID") = PlayerIdtxt.Value) Or (userfound = False) Then
    userfound = True
    End If
    rst.MoveNext
    Loop
    If (userfound = True) Then
    DoCmd****nSQL "DELETE dbo_tblPlayer.* FROM dbo_tblPlayer WHERE ([dbo_tblPlayer.PlayerId]=PlayerIdtxt.Value)"
    MsgBox "player was deleted succsesfully!", , "msg"
    rst.Close

    that code works
    your syntex gives me errors, i don't know why
    i think the problem is cous of the connections to the other tables
    so i will try to delete from them first
    tnx for your help

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    1. If the code I posted produces an error it's probably because you have no reference to DAO in your program (I did not know you were working with ADO).

    2. If when you write "i have some tables that are connected to each other" you mean that there are relationships in the database at the server level, then the problem might come from there. Without knowing more about how your system is organised it's difficult to point out why you have this error and how to solve it.

    Have a nice day!

  5. #5
    Join Date
    Mar 2004
    Posts
    21
    hei,

    thank's a lot for the answer
    we kinda figure it out..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •