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.

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?

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));

Open in new window

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

Open in new window

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

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

Of course, the data types must match.

/gustav

ASKER

They do. I've attached a capture of the sql setup. No unusual fields that odbc shouldn't be able to handle.

User generated image

ASKER

For reference here is what Access sees in design view.

User generated image

Then it probably is the missing timestamp field. I always append that as the vary last field of the table.

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

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

ASKER

Yes

Perhaps it is the unit Price. Can't you do with Currency/Money data type?

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

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

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

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

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

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.

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.

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

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.