asked on
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.
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?
UPDATE MultiImports SET MultiImports.[Unit Price] = 1.12 WHERE datevalue(Occurred) Between #2/10/2015# And #2/10/2015# AND ImportDate Is Null
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?
Microsoft AccessMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
bhieb
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
ASKER
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));
ASKER
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
Did you include a timestamp field in the table? ODBC is quite sensitive for missing timestamps.
/gustav
/gustav
ASKER
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.
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.
Of course, the data types must match.
/gustav
/gustav
ASKER
They do. I've attached a capture of the sql setup. No unusual fields that odbc shouldn't be able to handle.
![capture]()
ASKER
For reference here is what Access sees in design view.![access]()
Then it probably is the missing timestamp field. I always append that as the vary last field of the table.
Then relink.
/gustav
Then relink.
/gustav
ASKER
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.
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
Go to the table designer and add a field - you can name it timestamp - with that data type.
/gustav
ASKER
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.
ASKER
Just to humor you, I added a field call stamp with the timestamp type. Cleared the table, appened the data, re linked, same error.
OK. And ID is marked a the PK in Access?
/gustav
/gustav
ASKER
Yes
Perhaps it is the unit Price. Can't you do with Currency/Money data type?
/gustav
/gustav
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
tried hose/cnt which is 5,0 and it doesn't work either. changed price to 18,3, no impact there either.
And if you remove the ID, it works?
What if you apply a normal autonumber ID?
/gustav
What if you apply a normal autonumber ID?
/gustav
ASKER
removed the pk from ID. added a new autonumber field and gave it pk. still no luck
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
Which driver do you use? I mostly use the plain "SQL Server" driver with Access, and the native driver with Visual Studio.
/gustav
ASKER
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.
OK.
/gustav
/gustav
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
Thanks for the suggestions I'll split the points even though we couldn't track this one down.
ASKER
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.
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.
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
> 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
ASKER
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.