recordset locking

Question

I am having conflicts with a specific table in an MSAccess database due to multiple users.    I wish to change the locking to Pessimistic for that table.    Is there a way to do this programmatically?   I program in vb.net.

If not in by VS2017 program, how do I set the properties of that table to 2 or pessimistic in the Access database?

Answer 1

Not sure, but at the very least you would have to tell us what kind of middleware you are using to connect to the database.

I am not at all convinced more locks are going to make the user experience any better, but ultimately that is for them to decide.

---

-Tom. Microsoft Access MVP

Answer 2

It not clear if Access is ever touching this data, or if you are 100% only .net?

(The above is important – since this can be rather confusing if Access (the UI) is being used here.

If you using Access, then you have to set this on a form by form basis. (It is not a global setting despite a good number of articles suggesting as such).

So, you really for all practical use can’t set this from .net.

Keep in mind that access has two locking features.

#1

Default record locking.

#2

Open databases by using record-level locking.

Note that the 2^nd feature above is ONLY available from the Access UI – you cannot get at, or change this from DAO or ADO (and thus not from .net).

You “could” launch a copy of Access first – since the record-level locking feature is THEN SET in stone – any other user by any means will thus be forced into this “row locking” system. (and be careful – if on start up code you open a dao recordset for a persistent connection it WILL override the Access setting!!!

Keep in mind this #2 feature means that access cheats and simply expands all records to the size of one database page (so this option tends to cause huge bloating of the database size). Note also that his seeting does NOT lock reocrds, but if you choose #1 option to lock, then you get true row level locking.

However,  Access never really had true row locking, but always had page locking, and if you expand the record to the size of a database page, then in a round about way,  you get true row level locking if you THEN choose to lock reocrds during editing.

FYI:

Keep in mind that the “locking” feature in Access (first above) is not system wide. While the option to turn on locking is a setting (not to be confused with the true record level row locking setting) is done on a form by form basis.

So the main setting in Access actually sets the default for any new form you make – but existing ones remain un-touched. So you MUST set record locking on a form by form basis.

Again:

The above record locking is NOT to be confused with the row locking setting.

I do believe that if you using .net to edit the data (and not the access UI), then the oleProvider can open a query, update, and do so with record locking – I not done this, but I do believe this is possible.

So it not clear if you wanting to change the Access “UI” application from .net, or you just using .net, and want to lock records is a JET/ACE database?

If you do set a form to “lock” a record, then all users can look at the record, but if someone starts editing, then at that point all other users in the record will not notice, however, if they start typing and attempt to “dirty” the record, then the record selector bar in place of showing a pencil (dirty) will show a lock symbol like this:

And any keypress into the form will cause access to go “beep” and not allow editing. Thus no two users can edit the same record, and you thus “never” get that dreaded “record has been changed by another user).

So it just not 100% clear if the Access UI is to be used here. However, if it is, then you have to set each form – it is not a global wide setting. (at least not for forms).

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 3

I am sorry I have not been clear.   I am using programs written in VB.net to utilize an MSAccess database.    

I understand from what you say that I cannot lock the database from the Form in .Net.

I don't understand how to lock it in MSAccess; so, it will be locked when the VB.Net programs utilize it.   I am using a bound datagridview form to edit it.     You say I set a form to lock the record.    I don't understand how to do this as all of my experience has been with forms in Visual Studio.   I am using the 2016 (current) version of Access.

Under Client Settings, I have found Advanced and Default Record locking.    I see that I can choose "All records" or "Edited record" as well as "No locks".   Using the bound datagridview, one computer can edit the grid while a second grid has it open on each of the default record locking settings.    This is dangerous as the second user will have no idea of the changes made by the first user.

Answer 4

I did a workaround.   When a user enters the form, it checks a table showing whether the form is locked or not.   If it is not, the table is updated showing that it is locked.   If the table shows that it is locked, the form makes the datagridview readonly.   Upon leaving, the form updates the table to show it is unlocked.    This is not a great workaround, but it functions.

Answer 5

>>I understand from what you say that I cannot lock the database from the Form in .Net.

No, that is NOT what I am saying. I am saying that if users work and edit data using the Access UI, then what occurs will change.

We are confusing the development tool called Access here. This is a tool like Visual Studio that allows you to develop software, using Team Foundation Server for source code control, and all of the things you typical do in say Visual Studio. And with the access development system you can build reports, forms and write code in Visual Basic for Applications.

Then you have the Access database engine (JET or now the newer one called ACE).

It is BEYOND critical for this discussion that we distinguish between the two systems here. (the Access IDE/Development system and UI, and that of the Access database engine).

This is WHY I asked carefully if you using the Access UI or just the database engine. (you have now made this clear - you are ONLY using the Access data engine - not the development part of Access.

I asked this, because some of the settings available for the JET/ACE database engine are ONLY available from the Access UI and IDE – not from the data engine. In other words some of these settings ONLY WORK FOR AND APPLY when using the Access UI.

And I explained that from the Access UI, there are two options. And BOTH will NOT apply and effect you at this point in time (because it is clear you are only using the Access database engine, and you don't even care if Access is installed (in other words, you don't have to install access, but just the database engine). JET is installed by default on windows. But if you using the ACE format (accDB - access 2007 file format or later), then you have to install the ACE data engine. There is a separate download for ACE, or if you have the Access UI installed, then ACE data engine will thus also be installed.

You can re-read my original response if you need or want to grasp what these settings do.

Now that we are crystal clear of the context?

Yes, you can most certainly lock rows from .net if you are using the oleProvider. (this in effect is ado.net – vastly different then ado).

However, you can use the DAO or ADO object model from your VS project also. This would make some recordset code somewhat easier, but if you can, one should stick to the .net providers in place of ado or dao – since that’s likely what you done already.

I not tested .net locking against a access database, but it is explained here:

https://www.techrepublic.com/article/pessimistic-locking-solves-adonet-concurrency-problems/

So you have to wrap your update inside of a transaction.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 6

what is causing the issue is two users having the datagridview open at the same time; therefore, it is not a record locking issue, but a table locking issue.    The individual record is not causing a problem but a change to a record called up by #1 user and then changed by #2 user while still open by #1.    I fixed this by locking the whole grid when open by one user.    I also set a timer to keep a user from leaving the datagridview open when not using it.   This is designed to be equivalent to locking the table.