Database

1-Apr-12 9:14

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?

Eddy Vluggen1-Apr-12 22:43

1-Apr-12 22:43

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 Smile | :)

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 Suspicious | :suss:

Richard.Berry1004-Apr-12 18:02

4-Apr-12 18:02

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-MON4-Apr-12 23:18

JOAT-MON

4-Apr-12 23:18

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.

Richard.Berry1005-Apr-12 21:30

5-Apr-12 21:30

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()
            End Try
        End Using

        Return result


    End Function

modified 6-Apr-12 4:22am.

JOAT-MON6-Apr-12 8:12

JOAT-MON

6-Apr-12 8:12

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.

Eddy Vluggen5-Apr-12 1:23

5-Apr-12 1:23

1) Thanks - keeps me from posting answers that you already seen Smile | :)

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 Suspicious | :suss:

Richard.Berry1005-Apr-12 22:21

5-Apr-12 22:21

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 | :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 Smile | :)

Eddy Vluggen6-Apr-12 1:48

6-Apr-12 1:48

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 )

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

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 Suspicious | :suss:

Richard.Berry1006-Apr-12 1:56

6-Apr-12 1:56

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

Eddy Vluggen6-Apr-12 2:07

6-Apr-12 2:07

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 Suspicious | :suss:

Richard.Berry1006-Apr-12 2:16

6-Apr-12 2:16

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

Eddy Vluggen6-Apr-12 2:31

6-Apr-12 2:31

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 Smile | :)

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 Suspicious | :suss:

Richard.Berry1006-Apr-12 2:41

6-Apr-12 2:41

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.

Eddy Vluggen6-Apr-12 5:50