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