Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-18-2019, 01:08 PM   #1
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 357
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
SQL Server table not updateable

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...

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

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 06-18-2019, 01:11 PM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,980
Thanks: 34
Thanked 714 Times in 697 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: SQL Server table not updateable

Hi Mike. Does the SQL Server table have a primary key?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
mjdemaris (06-18-2019)
Old 06-18-2019, 01:22 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,391
Thanks: 106
Thanked 2,517 Times in 2,311 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: SQL Server table not updateable

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
mjdemaris (06-18-2019)
Old 06-18-2019, 01:37 PM   #4
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 357
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL Server table not updateable

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.
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 06-18-2019, 01:49 PM   #5
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,777
Thanks: 385
Thanked 238 Times in 208 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: SQL Server table not updateable

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...
__________________
I had the RIGHT to remain silent...but I didnt have the ABILITY. - Ron White
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
mjdemaris (06-18-2019)
Old 06-18-2019, 03:29 PM   #6
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 357
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL Server table not updateable

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!
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 06-18-2019, 03:32 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,391
Thanks: 106
Thanked 2,517 Times in 2,311 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: SQL Server table not updateable

On behalf of all of us, you're welcome

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-18-2019, 03:52 PM   #8
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 357
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL Server table not updateable

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/for...d.php?t=280228

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard

Last edited by mjdemaris; 06-18-2019 at 05:08 PM.
mjdemaris is offline   Reply With Quote
Reply

Tags
linked table , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updateable Reference Table smeltzjg Tables 2 08-16-2016 08:27 AM
Access 2010 front end to SQL Server 2012 Operation must use an updateable query SandyShin Queries 4 05-09-2013 10:35 AM
Recordset is not updateable - After upload to company server cjcobra General 1 01-28-2011 06:13 AM
Updateable Pivot Table Alternatives BARJRD Forms 2 06-17-2009 04:05 PM
query has one table but not updateable pbuethe Queries 2 04-01-2005 07:07 AM




All times are GMT -8. The time now is 12:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World