Access Update query giving Lock Violation messages

I've narrowed this down to an issue most likely with the ODBC connection or the SQL source. I'm trying to do a simple update on a linked SQL table.

UPDATE MultiImports SET MultiImports.[Unit Price] = 1.12 WHERE datevalue(Occurred) Between #2/10/2015# And #2/10/2015# AND ImportDate Is Null

Open in new window


When I do a currentdb.createquerydef from that sql and run it. It says cannot update dueto lock violations. It used to work fine. All I did was add a primary key field on the SQL table nchar(255) text.  I then deleted the link and re-linked it.  Inserts into the table work fine, and updates by opening the table and just typing work fine. So they recordset is definitely updateable. If I import the table instead of linking it, that works too.

I've went as far as creating a new access db and doing everything from fresh. Even tried a trusted dsnless ado link on the table. All no luck. I've done a compact on the DB, I've even restarted, made sure there were no lock files, and just opened and ran the query, failed right off the bat. There are no other forms or or any code in the access app that could be locking it, and if there were it would lock it when imported as well.

Any thoughts?
bhiebAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Don ThomsonCommented:
Have you tried setting up the query using the query wizard.  Once you get that working you can show the SQL equivalent and compare it to your code line
0
bhiebAuthor Commented:
Here it is. Other than some extra crap access always adds it is the same (and it doesn't work either). Note I changed the criteria to Occurred < 2-11-2015 just to simplify it even further. Still no luck.

UPDATE MultiImports SET MultiImports.[Unit Price] = 1.12
WHERE (((MultiImports.Occurred)<#2/11/2015#) AND ((MultiImports.ImportDate) Is Null));

Open in new window

0
bhiebAuthor Commented:
If I run the following on SSMS (sql manager). It executes without error. It definately appears to be  something about the ODBC link.
UPDATE MultiImports SET MultiImports.[Unit Price] = 1.12
WHERE Occurred<'2/11/2015' AND ImportDate is null

Open in new window

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gustav BrockCIOCommented:
Did you include a timestamp field in the table? ODBC is quite sensitive for missing timestamps.

/gustav
0
bhiebAuthor Commented:
hmm come to think of it I did change one field from text to datetime in sql. However it isn't required and is set to allow nulls. Not to mention the records being updated have a value in the field.

I guess I can do it as a pass through since SQL will run the query just fine, but I'd like to narrow down what the issue is if at all possible. Definately something between ODBC/JET and access that it isn't liking.
0
Gustav BrockCIOCommented:
Of course, the data types must match.

/gustav
0
bhiebAuthor Commented:
They do. I've attached a capture of the sql setup. No unusual fields that odbc shouldn't be able to handle.
capture
0
bhiebAuthor Commented:
For reference here is what Access sees in design view.access
0
Gustav BrockCIOCommented:
Then it probably is the missing timestamp field. I always append that as the vary last field of the table.

Then relink.

/gustav
0
bhiebAuthor Commented:
What missing timestamp field?  There are only 2 datetime fields, one is null one has a valid value. One was always null and this has worked fine for years.
0
Gustav BrockCIOCommented:
timestamp is a special binary format of SQL Server.
Go to the table designer and add a field - you can name it timestamp - with that data type.

/gustav
0
bhiebAuthor Commented:
There is no requirement for a timestamp field, I have literally dozens of tables and none of them have that.  This table never had it either and worked fine for 5+ years until the recent changes.
0
bhiebAuthor Commented:
Just to humor you, I added a field call stamp with the timestamp type. Cleared the table, appened the data, re linked, same error.
0
Gustav BrockCIOCommented:
OK. And ID is marked a the PK in Access?

/gustav
0
bhiebAuthor Commented:
Yes
0
Gustav BrockCIOCommented:
Perhaps it is the unit Price. Can't you do with Currency/Money data type?

/gustav
0
bhiebAuthor Commented:
Doubt it that field is the same as before, besides it is just a decimal 18,5 and it reflects that both in the linked table and in SQL.

Just for grins I tried to run a delete query, that one also fails with lock violations so it isn't just an update. Odd that the insert works fine.
0
Gustav BrockCIOCommented:
In Access, only Decimal can handle a precision of 18,5 and it is known to be buggy for table operations.

Can you update one of the other fields from Access?

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bhiebAuthor Commented:
tried hose/cnt which is 5,0 and it doesn't work either. changed price to 18,3, no impact there either.
0
Gustav BrockCIOCommented:
And if you remove the ID, it works?

What if you apply a normal autonumber ID?

/gustav
0
bhiebAuthor Commented:
removed the pk from ID. added a new autonumber field and gave it pk. still no luck
0
Gustav BrockCIOCommented:
Well, no more ideas. Can't replicate it here.

Which driver do you use? I mostly use the plain "SQL Server" driver with Access, and the native driver with Visual Studio.

/gustav
0
bhiebAuthor Commented:
Yep just plain SQL. I guess I can go the pass through route or just code it via ado. Like you I've seen my share of squirly things with access/jet/odbc, I'll chalk this one up to yet another "access" thing.
0
Gustav BrockCIOCommented:
OK.

/gustav
0
Don ThomsonCommented:
Make sure you have set Default Record Locking to "No Locks" under File (Office Button) | Options | Advanced (Access 2007 or later), or in earlier versions: Tools | Options | Advanced.
0
bhiebAuthor Commented:
It was set to no locks, and other SQL tables on the same SQL DB work just fine. There is some field data type or something about this particular table that ODBC/Access doesn't like. It is just 2 update queries, so I've just set them up as pass through, and used currentdb.querdef "myquery".sql = mysqlstring to get around it.

Thanks for the suggestions I'll split the points even though we couldn't track this one down.
0
bhiebAuthor Commented:
I'd like to post back in case others have this issue. It turned out to be the Key field on the SQL table. It was set to nchar(255). Since that is NOT a variable char it would always add spaces to the end so 123 becomes 123 +252 spaces.

Not sure what exactly about it that Access didn't like, but I'm willing to bet either it was the spaces or the length. Since I didn't really need 255, and definitely didn't want spaces in an Primary Key. I set it to nvarchar(100), then trimmed out the spaces in SQL. Low and behold it now works fine.
0
Gustav BrockCIOCommented:
Well, that's what I opted for here:

> What if you apply a normal autonumber ID?

I can see now, that I didn't specifically mention it to replace the key but I thought that what implicit in the wording "normal autonumber ID".

Anyway, thanks for the feedback. Hate mysteries.

/gustav
0
bhiebAuthor Commented:
In this case that wouldn't work without some extra programming. Since this is a vendor file I use their id number as a primary key, then we just download last 30 days each day. Having it set as key prevents duplicates. If I did an auto number I'd have to query the import file daily to exclude previously imported data, not impossible, but I didn't want to change it up.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.