How do you lock records in Access 2007

Richard Berry100

I posted a question on the same topic, a few days ago. I got some good advice, but now I need to know: 1) Can you explicitly lock a record in Access 2007 - and how? 2) Can you explicitly lock a table in Access 2007 - and how? 3) From a vb.net app using ACE to connect - how can I tell if a record or table is locked?

Lost User

Richard.Berry100 wrote:

I posted a question on the same topic, a few days ago

I'm too lazy to search the forum for that particular post. A link would have been nice :) Access isn't meant as a multi-user database-server. You can upgrade to Sql Express (which is free) using the "upsize wizard" that's embedded in Access.

Bastard Programmer from Hell :suss:

Richard Berry100

Thanks for your reply Eddy. 1) The link to the previous question was http://www.codeproject.com/Messages/4207612/How-do-you-lock-records-in-Access-2007.aspx[^][] 2) I am planning on moving to SSE but I need a quick fix for Access now. (I used a lot of parameterized queries, and Access allows '?'as a parameter name, while SQL server wants actual field names '@@name' and I have to update all the queries before I can move to SSE 3) I have searched Google, and cant find how to lock records, but I did find a post on another forum where it seems you do it in the connection string - but I cant find the syntax to use. I would alos assume the locking in OleDb (for Access) will be similar in SqlDb (For SSE)

JOAT MON

This may help you: Jet OLEDB:Database Locking Mode=1[^] I have seen these used in connection strings for Ace. Worth a try...

Jack of all trades ~ Master of none.

Lost User

Thanks - keeps me from posting answers that you already seen :) 2) It doesn't have to be the name of the field, but the names do have to match in the parameters. Using "@1" in the query and adding a parameter called "@1" will put that value on that position. 3) Access is a flat-file database, ideal for a single user. Sql Server is a database-server product, ideal for multiple users. It doesn't lock records, but pages. Never had much concurrency-problems in Sql Server. Had a lot of them in Access.

Bastard Programmer from Hell :suss:

Richard Berry100

Thanks JOAT-MON That did help! (I think...)

Provider=Microsoft.ACE.OLEDB.12.0;Data source= C:\myDb.accdb; Jet OLEDB:Database Locking Mode=1
'Locking mode: 0 = Page Level, 1 = Record Level

Do you have any idea how I can test this on my pc at home to see if the record is locked? My code to update the stock quantity is as follows, so I thought of running one instance of the app from Visual Studio and setting a breakpoint somewhere in the code, and then running a second instance of the app on its own from the exe and trying to update the same record, but not sure where I can set the breakpoint.

Friend Function AddStkReceived(ByVal TransactType As String, ByVal StockCode As String, ByVal WH As String, ByVal Qty As Double, ByVal Comment As String) As Boolean
Dim sucess As Boolean = False
If IsNothing(Comment) Then Comment = ""
Dim params() As String = {Qty, StockCode, WH}
Dim strUpdate As String = "UPDATE stock_qty SET Qty = Qty + ? WHERE StockCode = ? AND WH = ?"

    If Not DA.UpdateRecord2(strUpdate, params) Then
        sucess = False
    Else
        sucess = True
    End If

    If sucess Then
        If Not WriteStkHist(StockCode, Qty, TransactType, WH, Comment) Then
            sucess = False
        Else
            sucess = True
        End If
    End If

    Return sucess
End Function

Public Function UpdateRecord2(ByVal strUpdate As String, params() As String) As Boolean
Dim RecordsUpdated As Integer = 0
Dim result As Boolean = False

    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)
        cmd.CommandTimeout = 30
        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()
            If RecordsUpdated = 1 Then
                result = True
            Else
                result = False
            End If
        Catch ex As Exception
             result = False
        Finally
            cn.Close()

Richard Berry100

Thanks again! I really do want to move to SSE, but all the query strings in the app use '?' for all the parameters, then I have a generic function in a data layer, to which I pass the query string, and an array of parameter values. Then I loop through the array and add the parameters. So i need to change all the query strings in the app (there are lots of them :mad: )

Public Function InsertRecord2(ByVal strInsert As String, params() As String) As Boolean

    Using cn As New OleDb.OleDbConnection(cnStr)
        Dim da As New OleDb.OleDbDataAdapter
        Dim cmd As New OleDb.OleDbCommand(strInsert, cn)
        cmd.CommandTimeout = 30

        For i As Integer = 0 To params.Length - 1
            If params(i) = Nothing Then params(i) = ""
            cmd.Parameters.AddWithValue("@" & i.ToString, params(i))
        Next

My other concern is that the company uses Small Business Server, which I understand also uses SSE, and I'm terrified I'm going to mess up the server when I install another instance of SSE.... As it is I have installed SSE on my pc, and managed to mess that up by installing access using Windows Authentication, and now I can't enable sa even after changing the authentication to SQL Server and Windows Authentication Mode - But that's another issue :)

Lost User

Richard.Berry100 wrote:

Then I loop through the array and add the parameters. So i need to change all the query strings in the app (there are lots of them :mad: )

Seems you already have the code to add parameters. How much could you change using "search and replace" over, say, the weekend? :)

Richard.Berry100 wrote:

My other concern is that the company uses Small Business Server, which I understand also uses SSE, and I'm terrified I'm going to mess up the server when I install another instance of SSE....

..adding another instance of Sql Server would make the entire PC somewhat slower. Adding another server would be costly too. Is at a large database? How many tables are we talking?

Bastard Programmer from Hell :suss:

Richard Berry100

Quote:

Seems you already have the code to add parameters. How much could you change using "search and replace" over, say, the weekend? Smile | :)

Busy doing this now - Its a long weekend...

Quote:

..adding another instance of Sql Server would make the entire PC somewhat slower. Adding another server would be costly too. Is at a large database? How many tables are we talking?

There are about 20 tables, some of the tables have about 4000 records (stock_qty) In a month they added about 4000 rows to the Sales Order Detail table, each of which has a correspronding entry in a Stock History table The Access db is about 4MB

Lost User

Richard.Berry100 wrote:

Busy doing this now - Its a long weekend...

Aight, good luck.

Richard.Berry100 wrote:

There are about 20 tables, some of the tables have about 4000 records (stock_qty)
In a month they added about 4000 rows to the Sales Order Detail table, each of which has a correspronding entry in a Stock History table

The Access db is about 4MB

Instead of installing yet another Sql Server, you might also be able to put your tables on a database in their (existing) server. That would at least keep administration simpler (one server to manage is easier than updating two).

Bastard Programmer from Hell :suss:

Richard Berry100

Thanks - will consider installing on same server, but I'd get someone who knows a bit more about servers to assist! I was leaning towards them getting another PC on the network and put and instance of SSE (with different instance name to the default) on that... Perhaps a performance loss but less risk

Lost User

Richard.Berry100 wrote:

I'd get someone who knows a bit more about servers to assist!

Always a good idea. And do make a backup :)

Richard.Berry100 wrote:

I was leaning towards them getting another PC on the network and put and instance of SSE (with different instance name to the default) on that... Perhaps a performance loss but less risk

Both have some advantages and disadvantages; if there's a spare PC in the network, then that would be ideal; no risk of contaminating other data, a nice dedicated server (meaning traffic won't slow down other apps requesting data from another db on the same machine), and you'd be able to have all settings as required for your app. Then again, machines are expensive. If you move to an existing server, you probably have to use an existing account to connect, check the collation of the server, all kinds of things that cost time.

Bastard Programmer from Hell :suss:

Richard Berry100

Thanks for all the advice Eddy. If the app works properly, I'm sure they wouldn't mind investing in a mid range pc - and I guess the performance would be a bit better than with Access db as well.

Lost User

My pleasure. And yes, performance would increase - most notable change will be if you have complex queries; since the database is on a dedicated machine, the local machine won't have to do this processing anymore :)

JOAT MON

I haven't done any tests on record locking, so I may not be the best person to ask. but as I understand it, there are two type of locking (optimistic and pessimistic). Pessimistic only allows one user to use it at a time and will hold the lock. Optimistic, however, only locks the record at the moment it is updating then releases it. I am not sure what type of locking is implemented by the command line instruction above, but if it is optimistic it may not be possible to test it reliably. Sorry I can't be more helpful on that.

Jack of all trades ~ Master of none.