SQL Server table not updateable (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 10:25
Joined
Jul 9, 2015
Messages
424
Hello All,

I have one table that is giving me problems. Access is the FE, SQL Server 2014 is where the BE is.

I am currently using DAO to access the BE tables. I have one table that gives me an error stating the I cannot make changes because another user is already making changes. I've checked the FKs and constraints, closed and re-opened Access...:banghead:

I can make changes via SQL query in the SSMS.

I've relinked the table...not sure what to check next.

Thanks in advance.

Mike
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:25
Joined
Oct 29, 2018
Messages
21,358
Hi Mike. Does the SQL Server table have a primary key?
 

isladogs

MVP / VIP
Local time
Today, 17:25
Joined
Jan 14, 2017
Messages
18,186
You can also get this error when you try to update a SQL table containing a boolean field without a default value
Unlike access, SQL boolean fields can have 3 states: true/false and null (if there is no default)
If so, Access doesn't know how to handle the null booleans and creates a write conflict error as you described.

If that is the case, the solution is to set a default & change all existing null boolean values to the default in SQL Server
 

mjdemaris

Working on it...
Local time
Today, 10:25
Joined
Jul 9, 2015
Messages
424
Yes, there is a primary key, marked as identity, so it should be ok, as long as i don't try to insert one, lol.

I will check on the boolean fields...

Update: It won't let me delete a record from Access either, using the record selector on the datasheet view.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:25
Joined
Apr 27, 2015
Messages
6,286
I had a similar problem. The answer turned out to be a timestamp field. As soon as I added one, all my problems went away...
 

mjdemaris

Working on it...
Local time
Today, 10:25
Joined
Jul 9, 2015
Messages
424
I do have a time stamp field, but there was no default setting. I ended up dropping the table and recreating, with a proper time default setting and all seems to work fine so far.

The boolean fields all had default settings, so I'm going with the time stamp field.

Thanks for your tips!
 

mjdemaris

Working on it...
Local time
Today, 10:25
Joined
Jul 9, 2015
Messages
424
After attempting to run an insert via VBA a few times, it still fails.

So it appears the problem is in the VBA, perhaps.

The first error that appears in the Errors collection states that the Connection is busy with results for another command.

Researching...

Update:
I believe the fault was my own, as I tried to insert a number that made the FK constraint unhappy.

However, the error mentioned above, I guess at this, may have been caused by a second record set open at the same time - but, this record set was not tied to the same table, or any related tables. Ah, I just read back through the offending code and found the third record set does in fact reference a View that uses the import target table, so this may be where the busy signal came from.

I am simply(!) working on an import solution from Access BE to SQL Server.

Being able to see each error has been very helpful.

Thanks again. BTW, Andrew Couch's book: Access 2010 VBA Programming: Inside Out is a good read.

https://access-programmers.co.uk/forums/showthread.php?t=280228
 
Last edited:

Users who are viewing this thread

Top Bottom