Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Automation _ Used By Another User

Posted by: alorenzini Aug 9 2019, 09:52 AM

I have an Access front end and a SQL Server backend. THe front end is on about 10 other desktops including mine. When I run the application on my machine it works just find I can add and edit records. But when some one else starts the application, it throws an error (I have attached both of them). Basically it is saying that the record cannot be saved because it is being used by another user, which it is not. I have another user that the application works just fine too. This even happens when there is only one person with the application open. I am kind of leaning towards some sort of account level security issue but don't know. I checked the logins on the SQL Server and they all have identical permissions.



 

Posted by: theDBguy Aug 9 2019, 10:53 AM

Hi Art. This error also comes up if you make a manual change to a record while at the same time it is also getting updated by code. Do you know if this is the case with your db?

Posted by: HansUp Aug 9 2019, 10:53 AM

Check whether the problem is eliminated if you add a timestamp (rowversion) field to the SQL Server table.

Posted by: alorenzini Aug 9 2019, 11:24 AM

No the code does not update the record.

Posted by: alorenzini Aug 9 2019, 11:25 AM

All tables have a datetime field with a default of now. It is updated every time the record is changed.

Posted by: isladogs Aug 9 2019, 02:15 PM

Does the SQL table contain a Boolean field with no default value?
If so, trying to update the table will create a write conflict error where the field value is null.
This is because Access cannot handle nulls in Boolean fields where SQL Server can do so.

The solution is to change all boolean nulls to e.g. False then set the default to false.
Do that for each Boolean field in each linked table to prevent issues in future.

Posted by: alorenzini Aug 12 2019, 11:33 AM

That was exactly the problem. Thank you.

Posted by: isladogs Aug 12 2019, 02:02 PM

Hi
You're welcome.
I had exactly the same problem over 10 years ago. It took me ages to identify the cause but I've never had the problem since applying that fix.

Posted by: alorenzini Aug 12 2019, 02:33 PM

I wonder if there is an app that can list the default properties of all the tables in a database. I had to go through more than 75 tables to find the culprit.

Posted by: theDBguy Aug 12 2019, 02:48 PM

Hi Art.

QUOTE (art)
I wonder if there is an app that can list the default properties of all the tables in a database. I had to go through more than 75 tables to find the culprit.

See http://www.accessmvp.com/thedbguy/demos/listfields.php...

Posted by: isladogs Aug 12 2019, 02:51 PM

You can also use the built-in Access database documenter for this purpose.
However surely you could have checked the tables in use when the error occurred.