Encryption base and row level locking (1 Viewer)

[radek225]

I have application with linked base (*accdb) on LAN NAS storage for few users, designed with row level locking, but since I've made encryption my database, MS Access disabled my row protection and now i have problem with users "I can't edit my box because someone use it" - None use it:/.

I think Ms Access enable page level locking instead of row level locking despite the fact that in base option row level locking is set and every form row level locking is set too. Am I right?

Is there any solution to get around with?

According to page level locking, is there some scope of rows which are locked?

 

[gemma-the-husky]

why are you using record locking.

I would probably
a) not use record locking
b) look at my code to limit the time in which a lock is held to the absolute minimum
c) and possibly review my locking strategy.

 

[radek225]

why are you using record locking.

I would probably
a) not use record locking
b) look at my code to limit the time in which a lock is held to the absolute minimum
c) and possibly review my locking strategy.

Thank You for your insight



Please see attachment. If I disable option "Open database using record-level locking" then Access automatically switch to page-level locking, right? And that's mean locking all records which are next to editing item. - No one know what is a scope of records and which records are exactly locking by Access.

How could I define time to locking editing record using vba?
For recordset, sql command or operation on a form?

 

[static]

If you only have a few users you shouldn't have problems with record locking unless update queries are running constantly or something.

 

[radek225]

If you only have a few users you shouldn't have problems with record locking unless update queries are running constantly or something.

10 users and I have problem with locking. Records which are not editing are locking - it seems to page level locking instead of record level locking because I have encryption database. So now I need to find a way to get around

 

[gemma-the-husky]

you should use no locking.

the trouble is, if you use locking, then someone can open a form and lock the record, and then disappear fro lunch, causing problems for all the users.

you also get the cluster locking effect you are seeing.

because of this, and other locking problems, you have to be very careful with the way you as programmer handle the locks

it's far, far better to have no locks, and rely on access to manage your data with "optimistic locking". You won't have problems with "no locks"

 

[gemma-the-husky]

In the image you posted, if you UNCHECK the box you highlighted, then you are using the setting in the option group above - "NO LOCKS".

It's perfectly safe.

 

[The_Doc_Man]

If the BE file is native Access then you can NEVER turn off the locking of a single disk buffer regardless of the actual size of the record. (At least, not that I recall.)

As to locking, particularly when dealing with a BE linked table, ALWAYS use optimistic locks. You might further add a safeguard (if you have not done this already) by using a construct such as this:

Public Sub WhosOnFirst(yada yada)

Dim  dbDAO as DAO.Database
Dim  lRecAff as Long


'''

    SET dbDAO = CurrentDB

    On Error GoTo ScrewedThePooch
    dbDAO.Execute "{sql action-query statement goes here}", dbFailOnError
    lRecAff = dbDAO.RecordsAffected

    If lRecAff < 0 Then GoTo OperationFailed

OperationSucceeded:

...

    Exit Sub

ScrewedThePooch:

    lRecAff = -1
    Resume Next

End Sub

This construct includes an automatic rollback if the operation fails, so you protect the database from bad SQL, too!

 

[David92595]

Do you have a timestamp field in the table?

 

[radek225]

Do you have a timestamp field in the table?

No, should I? Why do I need?

 

[David92595]

I didn't see anywhere that you specifically mentioned that you had a split database with a SQL back end. Your issue just reminds me of when I made the switch to a SQL BE and an Access FE. With a SQL BE you must have a timestamp field or you get the exact issue you are reporting. Truthfully, it's been so long since I've used access as a BE I'm not sure if you need one.

 

[radek225]

I didn't see anywhere that you specifically mentioned that you had a split database with a SQL back end. Your issue just reminds me of when I made the switch to a SQL BE and an Access FE. With a SQL BE you must have a timestamp field or you get the exact issue you are reporting. Truthfully, it's been so long since I've used access as a BE I'm not sure if you need one.

yes I have sql back end. What is idea for timestamp? Just creating a column with date? how it's working?

 

[David92595]

In SQL there is a timestamp datatype. You can give the column any name you like. Though I always call mine TimeStamp...just to keep it simple.

Once you create the column in SQL, update your access table(s) with Linked Table Manager under the External data tab. That's all that you'll need. Whenever a record is modified it will automatically update the timestamp in the backend. You do not need to add the field anywhere in your forms.

 

[radek225]

In SQL there is a timestamp datatype. You can give the column any name you like. Though I always call mine TimeStamp...just to keep it simple.

Once you create the column in SQL, update your access table(s) with Linked Table Manager under the External data tab. That's all that you'll need. Whenever a record is modified it will automatically update the timestamp in the backend. You do not need to add the field anywhere in your forms.

Ok but this new field only show me when someone change or add new data, but it doesn't help me solving problem locking record.