What happens when two users try to access or update the same record (Access)

Richard Berry100

I have a vb.net stock control app that is installed on about 10 workstations. They read/write data to an Access 2007 DB on a server. For all the database interactions, I use routines like the one below. If the function returns false, the I display the error. The stock figures are getting all messed up. I have checked that all the transactions work correctly on my pc - no problems at all. The users ASSURE me that they have never seen any Error messages (I have also checked that should a function return false, that the error is displayed.) My question: 1) What happens if two users try to access the same record at the same time? 2) Would the code below throw an exception if the database trasnaction failed?

Public Function UpdateRecord2(ByVal strUpdate As String, params() As String) As Boolean
Dim RecordsUpdated As Integer = 0
Dim cnStr As String = "Provider=" & Provider & "; Data source= " & _Database

    Using cn As New OleDb.OleDbConnection(cnStr)
        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        Dim cmd As OleDb.OleDbCommand
        cmd = New OleDb.OleDbCommand(strUpdate, cn)
        For i As Integer = 0 To params.Length - 1
            If params(i) = Nothing Then params(i) = ""
            cmd.Parameters.AddWithValue("?", params(i))
        Next
        da.InsertCommand = cmd
        Try
            cn.Open()
            RecordsUpdated = da.InsertCommand.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            frmMain.Err.DataMethod = "UpdateRecord"
            frmMain.Err.DataErr = ex.Message
            Return False
        Finally
            cn.Close()
        End Try
    End Using

    If RecordsUpdated = 1 Then
        Return True
    Else
        frmMain.Err.DataMethod = "UpdateRecord"
        frmMain.Err.DataErr = "No Records updated"
        Return False
    End If

End Function

PS: I am trying to migrate the code to work on SQL Server 2008 Express...

Mycroft Holmes

Your PS indicates you are making the correct decision as to the future. Access (used to) locks a "Page" of records when required, not sure the size of the page but it will include more than 1 record at the end of the table. Additional attempts to access the locked records will get an exception. You need to insure that this is likely to be a real world problem, in the 90s I had the same environment and never had a contention issue in a number of years of operation (we went to SQL Server eventually).

Never underestimate the power of human stupidity RAH

Peter_in_2780

Further to Mycroft's answer, IIRC Access supports more than page-level locking. At least some versions can also do record level locking (and maybe no locking at all, which would cause your problems.) I suggest a quick google/MSDN search on "access record lock". [disclaimer: It's been so long since I played with Access that I've forgotten more than I ever knew.] HTH Cheers, Peter

Software rusts. Simon Stephenson, ca 1994.

Mycroft Holmes

I was not aware that they ever achieved record locking, we never had any contention issues so I forgot about it, it was after all the 90s and I suppose even Access has had some improvement since then!

Never underestimate the power of human stupidity RAH

Peter_in_2780

My memories are VERY dim, but I'm sure Mr G and Mr W know.

Wikipedia says:[^]:

In Jet versions before version 4, a page locking model is used, and in Jet 4, a record locking model is employed.

Can't remember how you set it up for locking or not. Now for something that belongs in Coding Horrors, except it doesn't really qualify as coding: The weirdest db corruption I ever had to fix was a shared dB4 database, using Codebase. In this setup, index files are "external", and some smartass self-appointed admin managed to have two of the six clients running from the shared db files on the server but with LOCAL C drive index files. :mad::mad::mad: Cheers, Peter

Software rusts. Simon Stephenson, ca 1994.

Richard Berry100

Thanks for your reply. It seems from your answer that an exception would be thrown/caught in my code. I am leaning more towards the idea that MY CODE is causing the problem and has nothing to do with Access. I have to sort this out before I even think of SSE... Let me visit Google and then possibly post another question on that...

Richard Berry100

Thanks Peter. After some Googling on record locks, I think MY CODE is the problem, not Access.

Bernhard Hiller

Log the exceptions - that is better than asking (and believing in) the users. With Access, you must make sure that the parameters are added in the correct sequence, they all share the same name "?".

Richard Berry100

Thanks Bernard! Very good suggestion.... I'll implement some sort of logging. Thanks also, I did check that the parameters are added in the correct order. I have tested and tested the code over and over on my laptop, and I don't get errors, this is why I'm leaning towards some sort of concurrency issue. I also checked the locking settings on the Database.... Oops - they were set to None!!!!

Peter_in_2780

I wrote:

maybe no locking at all, which would cause your problems.

Richard.Berry100 wrote:

I also checked the locking settings on the Database.... Oops - they were set to None!!!!

No further comment required. Peter

Software rusts. Simon Stephenson, ca 1994.