Run-time error 3197 when attempting to update record via DAO recordset

Question

I've got a Microsoft Access 2016 application, currently running with a SQL Server 2019 backend. I'm having issues attempting to use a recordset.update to save changes to a particular record in my products table. I'm entirely self-taught and have been coding with vba for around 18 months, came across this issue with bit fields in the past, but I can't see what's causing it this time.

The error:

Error 3197: the microsoft access database engine stopped the process because you and another user are attempting to change the same data at the same time

Things I have tried:

1. The table has a primary key

2. Most records in this table allow me to edit and update the changes (there's a random selection that will not work)

3. All bit fields have default values set to 0

4. I've tried only updating 1 of the many fields to see if that effects the outcome, it doesn't, no matter if I'm editing a integer field only, or multiple mixed types, it still occurs

5. I can directly change the values of any field in this table using a SQL Query (pass through or via Access query designer)

6. I cannot change any value on these records (others are fine) via the table directly (using ms access table view, I get the Drop Changes / Save to clipboard dialog box)

7. Although this does happen when using this particular function via a form, even when running the code directly via the immediate window in the vba editor, I get the same error. But I ensured that the record locking used by the form was no locks.

8. The default open mode is set to shared (for the db, via Options -> Client Settings)

9. The default record locking is no locks (for the db, via Options -> Client Settings)

10. Currently using Row-Level Locking (for the db, via Options -> Client Settings)

For reference, the code is simple and straightforward enough:

Dim dbs As DAO.Database
Dim productRS As DAO.Recordset

Set dbs = CurrentDb
Set productRS = dbs.OpenRecordset("Select * From tblProducts WHERE ProductID = " & ProductID, dbOpenDynaset, dbSeeChanges)

With productRS
.Edit
!Name = someName
.Update
End With

rs.close
dbs.close

set rs = nothing
set dbs = nothing

I also have a RecordTimestamp field (datetime2(7)) that uses GetDate() as a default value for the record, and it's been set this way from the beginning of the project which leads me to think it's not an issue with this field. But I don't fully understand how exactly sqlserver and access remember/determine if a record is locked/being updated or if there's a way I can clear all these pending/non-existant locks.

Thanks in advance if anyone can think of something that would be causing this issue.

Answer 1

There's no need to use a recordset for a simple update. Just use Database.Execute

dbs.Execute "Update tblProducts " & _
    "Set Name = '" & someName & "' " & _
    "WHERE ProductID = " & ProductID & ";"

Debug.Print dbs.RecordsAffected & " record(s) affected."

If you insist using the recordset to perform the update, try changing the select statement from

Select * From tblProducts ...

to

Select Name From tblProducts ...

Why bother dragging data back from the server that you're not planning to use?

Answer 2

You need to add what is called a rowverison column to the database. now perhaps the greatest award in history goes to the folks for having called this a "timestamp" column, thuse the WORST POSSBILE name!

The reason of course is a timestamp column HAS ZERO to do with date time, and is a VAST difference of a column then that of a datetime column in SQL server.

Timestamp data type column is NOT a date time column

It is a column that you NEVER EVER in ANY WAY and in ANY context WILL EVER be set, or touch or modify by you, or by any code. And you NEVER have to set a default value for this row version column, and you don't ever try to touch or modify this column - and that includes your code, sql server side code, and that also includes NOT having a default set for this timestamp column, which of course is HOW you create and obtain a ROWVERSION column in Access.

it is a column that DOES NOT have a default. It is in fact a binary blob with a checksum value.

To be 100% if not 1000% clear?

You are being told to add what is called a ROWVERSION column. To create such a column, you create a column of TIMESTAMP data type. This is NOT datetime column, and you are NOT to create a datetime column, nor are you to set a default for this row version column. Nor are you to adopt some datetime column with a datetime(2)7 format.

Of course any floating point columns also need a default value of 0, and they should not be null in addition to having that defaulted value.

Last but not least:

If the floating number values in that column have been set outside of access, then rouding issues can crop up.

As a result when access attempts to check/test if the record been changed, it does a FIELD BY FIELD data compare, and it fails.

However, introduction of a ROWVERSION column (you use data type timestamp), then access forgoes this column by column "test" and then uses that TIMESTAMP column in one operation. Thus no column by column compare need occur anymore, and thus things like floating point numbers and due to rounding compares are not required anymore.

As noted, without question, the name for the data type of TIMESTAMP does get the worlds BEST award for the worst possible name. To MS's credit, there newer documentation is referring to the timestamp data type column as ROWVERSION, but the damage was and has been done. And we for ever more live with this mess.

So, it is VERY important to keep in mind that this "rowversion" column of data type = timestamp has ZERO ZERO ZERO to do with datetime.

Hence, one can't confuse a datetime column with that of a timestamp (aka: row version) column, they are vast different.

so to fix this problem, you will read weekly posts here on SO and other boards. The simple recommendation here to for you to add a timestamp column, of which you now realize has nothing to do with time, or date, or in fact with a datetime column.

As noted, that rowversion column does not have a default, and you never set any such default in sql server, or in the access side of things. You don't have to and in fact as a general rule can't modify this row version column. And, you don't set a default, and it is simply a binary blob column used to determine if the record been changed.

And as noted, having such a row version column allows access to forego and to NOT have to do a column by column compare to determine if a record been changed.

So this weekly here is another post for the last 20 years?

And the solution for those 20 years?

You make sure there is a PK.

You make sure you default bit fields to 0 - don't allow null.

You should also make sure number (floating/real) columns also have that default of 0.

And last but not least, you also add a rowversion column, of which you need to choose "timestamp" as the data type to get and obtain a rowversion column in SQL server.

And as noted this name and type of column has ZERO to do with a datetime column.

so, like the other frequent posts - about 1-2 times a week for the last 20 years?

Try adding a timestamp column as per the many long years of recommending to do this as fix for this issue.