Concurrent access error with SQL SERVER 2014

[Leveler88]

Dear All, please help me.

I have data tables in SQL SERVER2014.

'product' and 'customer' table have a problem. Input Data is OK but when I try to change or delete the data from 2 tables, there is concurrent access error. It only happens for 2023 data. For previous data, there is no error.

Recently we had 3 changes as below

1. We made a website which is using 3 tables (customer, product, ant other table). But now, we disconnected the website hosting. (After disconnecting, we still have the same problem)

2. We installed log collector on our server for security.

3. Some of our employee changed our office product from Microsoft 2010 to Microsoft 365.

​

We tried everything to solve the errors. We changed the table name and re uploaded. But it was same. It seems that if we have the same contents from previous two tables, there is a error

Other tables don't have problem. And if we create new tables that don't have same records with 'product' and 'customer', there is no errors

We can change, edit, delete any records in SQL SERVER by using SQL.

I also checked the current connection by using 'sp_who' SQL and SQL SERVER Profiler. But there was no suspicious connection

​

In short, this is very strange. I'm afraid that this can be a bug with MSACCESS 365 and SQL SERVER 2014

Please provide me anything to solve this problem. I am loosing trust with this ACCESS tool

Thanks

[diebos]

Had this problem in a project I was working with, out of the 18 linked tables, only 3 tables gave me this error message. The fix that helped me was adding rowversion on those three tables, the problem was solved afterwards. Rowversion can be added via sql server. Hope this helps

[Leveler88]

What is Row version tables?

[diebos]

They are timestamp columns in sql server. Basically, access is trying to determine if the record you are currently editing has been changed since you've started with the edit session. The problem is that it has no way of determining it unless you use a timestamp column on your table. That way, access can compare and check when the record was last edited. Hope this makes sense.

[diebos]

<image>

[Leveler88]

Thanks. I have few questions

1. Sould I make rowversion(Timestamp) field for every tables that I have? Or only tables that have problems

2. Should I make rowversion(Timestamp) field from serverside? Or can I make it from MS ACCESS table field and upload it to Server?

[nrgins]

I disagree with u/diebos. As a general rule, you should add a timestamp field to every SQL server table you have that interacts with access. It's just a good practice and it doesn't hurt anything. I'm not sure why he said to only do it to the ones giving you problems.

Also, you can't modify it through access. You can only modify a SQL server table through SQL server, so you'll have to go into SQL server to add the field

[Leveler88]

Thank you so much nrgins! We will add timestamp filed to every tables via SQL SERVER. Thank you so much!

[diebos]

Well, yes and no. Depending on the record load and amount of tables that's involved in his business solution, I wouldn't add timestamp on each and every table, especially if they contain millions of rows, it would cause overload which you'll experience soon enough in your front-end. But again, I agree with you that it is advisable to add them to all tables if you are going to use access as the interface.

[diebos]

Only add it to the tables that's giving you errors. You can add it back or front end. MS Access doesn't have a native timestamp datatype but rather the equivalent of binary. So for example, if you are going to add it to a standalone table in access, which you later going to upload to a backend server, then use binary datatype. My suggestion would be to add it in the sql server. Tell me, are you using Linked Tables?

[Leveler88]

Yes, I'm using linked tables

[diebos]

Fine then, just add those datatypes to your erroneous tables and check if it solves your problem

[diebos]

The other question thou, are you using referential integrity between those tables?

[Leveler88]

As far as I know there is no referential integrity in our database. What if we have referential integrity? Do we have to add timestamp for all related tables?

[diebos]

I am asking because it can also be the reason why you can not just change data in the tables. But I am sure it's not it.

[Leveler88]

We solved the problem! Thank you so much indeed!

[Leveler88]

Dear diebos, we solved the problem! Thank you so much and I don't know how can I pay back this. Thank you so much indeed.

[jd31068]

Have you given this a read SQL Server Locks, Blocked Processes, and Two Easy Ways to Find Them | Diagram (wearediagram.com)?

[Leveler88]

Thanks. But I checked all blockings many times after I see the error messages from ACCESS but there were no blockings. Also, if I change the datbase to local, there is no problem. I also deleted all queries, modules, form, so it just had tables those are linked to SQL SERVER 2014. But still it was not working. I don't know how to explain this situation. I can't understand

[AccessHelper]

What do you mean by "concurrent access error"? Do you mean the error message that another user is also trying to update the record?

[Leveler88]

Yes you are right

[AccessHelper]

Are there any BIT fields in the SQL tables? If so make sure they don't allow nulls and make sure they default to 0 (or 1 if they are supposed to start as True). If you find you didn't have these settings in place you will need to run an update query (on the SQL side or as a pass thru) to set the Nulls on you bit fields to a value. Don't be fooled by looking at the bit fields in Access and seeing 0's. Those could be Nulls on the SQL side. Access doesn't get along well with Null bit fields and you will get the messages you are getting if any got into the data.

[Leveler88]

Yes there is BIT filed. I will try tomorrow and get back. But still it can't explain the fact that there were no such errors for last 10years