Read/write lock record and test for nonexisting record

[twgonder]

I have these two lines in a VBA function:

...
  SqlToDo = "SELECT * FROM tblc_38_SeqId WHERE UseTbl=""" & aUseTbl & """ And UseFld=""" & aUseFld & """"
  Set rs = dbs.OpenRecordset(SqlToDo, , 3)
...

And the SqlToDo variable looks like this when executing:
SELECT * FROM tblc_38_SeqId WHERE UseTbl="tbl_39_Entity" And UseFld="ID"
(UseTbl and UseFld are in a nonduplicate, composite index, so only one record is read.)

It seems to work, as far as reading the record, but I have a few questions, not sure what the M$ documentation is saying:

1) Should the "3" argument in Set read and write lock the record (I need to increment the value of one field)?
If not, what is the correct way?
2) I tried changing the record in the datasheet view, and it let me, so I'm guessing it's not really locked.
3) How does the record get unlocked (with a write?)? If I don't write the record back, will it still get unlocked with the rs.close before the exit funtion?
4) Is there a way to test that there wasn't an actual record read other than an error when trying to do something with a rs!field? (Use rs.EOF?)
5) I want to write some values back to the record, what does a statement look like to do that (if not just change the RS! field and do rs.Update)?

Thanks!

[Micron]

I think you're trying to set options parameter with a lock parameter value since 3 is not a valid value for options parameter. Perhaps you are missing a comma before the 3?
So that should explain 1 and 2?
3- if you open rs locked then AFAIK it is locked, but yes the lock (if there is one) should be released when the rs is closed.
4 - rs.recordcount is almost always 0 if it contains no records.
5 - if not locked, then yes rs!fieldName = something and so on, then rs.Update

[twgonder]

I think you're trying to set options parameter with a lock parameter value since 3 is not a valid value for options parameter. Perhaps you are missing a comma before the 3?
So that should explain 1 and 2?
...

Here's where I get confused, reading this:
RecordsetOptionEnum enumeration (DAO) | Microsoft Learn
Which is a sub of this:
Database.OpenRecordset method (DAO) | Microsoft Learn

I think I read somewhere that you have to sum the values in arguments to do what you want.
1 + 2 = 3 so I used three, but reading more carefully, 2 is table-type only, so that won't work for the SQL.
You can't add more commas, since those are arguments (1,3) in an argument, pushing the 3 into argument 4.
So, what would be the set command to get the record locked when read, and prevent others from reading or writing the record?

[CJ_London]

think your problem is preventing other users from reading the record whilst the recordset is 'active'. Never had to consider what the implications of preventing other users from reading a record would be - would the system freeze until the record can be read? or just be ignored? or have a message of some sort to explain that their 'list' does not include some records. Suggest experiment with values 1 and 2 separately and see what the effect is. Would have thought if you deny read, then deny write is irrelevant since if they can't read it, they can't write it

2) I tried changing the record in the datasheet view, and it let me, so I'm guessing it's not really locked.

if you've locked it, you can change it, locking means others can't change it.

3. The record is not locked until you start editing it (i.e. start to change a value) and is unlocked when you commit the changes or undo. You have a visual check in that the record selector will display a pencil icon when in edit mode. See this link https://support.microsoft.com/en-us/...7-97fe0568019a

Suggest review the dao recordset.edit option to start an edit process without waiting for the user to start editing
https://learn.microsoft.com/en-us/of...dit-method-dao

and review editmode
https://learn.microsoft.com/en-us/of...e-property-dao

4 - recordset defaults to first record on open - if there is no record then EOF=true

5. If all you are doing in modifying some field values and there is no user interaction, why not just use an update query?

[twgonder]

think your problem is preventing other users from reading the record whilst the recordset is 'active'. Never had to consider what the implications of preventing other users from reading a record would be - would the system freeze until the record can be read? or just be ignored? or have a message of some sort to explain that their 'list' does not include some records. Suggest experiment with values 1 and 2 separately and see what the effect is. Would have thought if you deny read, then deny write is irrelevant since if they can't read it, they can't write it

if you've locked it, you can change it, locking means others can't change it.
...

Thanks for sharing what you know on the topic. With the M$ way of doing things, I'm never sure how to describe what I need. And I get heat if I use the wrong term here or there. So, I often explain things in "old tech" ways, even though it's all the same thing with just a different name or approach.

Anyways, I was finishing up a project that was in some of my original posts, how to have a custom auto ID (not the Access autonumber) that would identify a record's "owner" across the network of servers so as to avoid duplicate id's without using a GUID. Project now successful.

Although the code is used in a form, it's in a sub two levels down from the form module. Yes, stop another from reading while the record (auto number record, not the form record) is being updated. However, I think there are situations where a write/delete can happen without a read, so those need to be handled too. This is one of those situations where complex logic is needed beyond what an update query could easily handle, but 30 lines of VBA can. I'm still testing but I think I found a solution by using the 4th argument of the .OpenRecordset.

[CJ_London]

Think you need to do some experimenting to test your scenarios. Easy enough to do, create a table and two queries based on that table (or forms using a recordset created with you lock parameters). Open both queries/forms and the table. Now start to edit a record in one of the queries but don’t leave the record. Now go to the other query/form and start to edit the same record. Go back to the first and save changes, go back to the second and save changes- what do you see?

repeat the exercise but edit and save changes in the second query/form then go back to the first and save- what do you see?

Repeat again but this time bring the table into play - that will be the nearest you can get to changes by an action query or similar - alternatively run an update or insert query whist ‘editing’ either or both query/forms

[twgonder]

@ CJ_L All done and working great, thanks!
I've got two VBA routines:
One that locks a record for a form outside of the Access locking system, so it's before entry, not after. It's handled by using a table.
The other routine gets a specialized auto number that stays sequential, that's used before update in a form. It also uses a table.
Each of those needs to lock their record in the respective tables as they both get hit by multiple users.
I'll spare the details, because as one once told me, this isn't Facebook.