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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.