How to handling a locking issue in a BE sql server and FE access environment.

Jack 0 Reputation points
2023-10-18T13:46:52.6966667+00:00

I have migrated a DB from an enviroment with access as backend and access as frontend to sql server as backend and still access as frontend. All the tables and views are of pass through types in Access.

My problem is with the linking table in a many to many relation. The linking table contains columns which are not the linking columns. These columns I can edit from access directly on the table. However, if I create a new row in the linking table in Access, I am told after the creation of the row, that I am in a writing conflict with another user if, I change values directly in the the extra columns in the table, which is not the case in my development environment. Just to clarify, when I write that, I am making a new row in the linking table it means that I also is making a new row in one of the other tables.

If I update the linking table from sql server, I have no issue.

If I create the row in the linking column in the sql server, I still have the same issue, when I am trying to update extra columns in the linking table.

In my first setup of the above solutions. I used the exact same code for creation of a row in the linking table without issues. The only differences is, I pressed the executing button from another form than the current, but the form is containing the linking table as it does now. The sql server table looks the same as the current, excecpt I have a few extra columns now.

In the backend access environment environment, I do it the same way as now, I don't get any issues.

The conclusion might be that access somehow puts a lock on the linking table, when I execute the code from the form now, however, I don't understand why it doesn't do it in my first setup with sql server as backend.

I don't understand why the lock is not released after access has been closed down and reopened.

I hope somebody knows more about this. I have tried browsing on the net without success

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
312 questions
{count} votes