Access 2007 Lock Violation

[JudeD]

On Vista machine, in Access 2007 with a linked SQL Server database... Append
queries work just fine, but attempts to do an Update query result in Lock
Violation. On individual PC, so no conflicts with others. Closed SQL Server
management Studio so DB is not conflicted. Checked permissions in SQL Server
and Access. Made Update query VERY simple - just one table, simple select,
simple change - and still get Lock Violation. Any ideas? Thanks.

 

[John Spencer]

Does the SQL table have a TimeStamp field? If you are using ODBC to
link to the SQL table, you need a timestamp field in each table you want
to modify.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JudeD]

John,
Yes, ODBC is used to link to the SQL table.
I added a TimeStamp field to the table, relinked it, and it made no
difference.

Any other ideas?
Thanks,
JudeD

 

[John Spencer]

Not really. I assume you have a primary key defined on the SQL table or
you defined a primary key when you linked the table.

Beyond that I am at a loss. Haven't really done much with 2007 yet.
Heck I just got some customers off of 97 this year. The only reason
they moved was that they were no longer able to get copies of Access 97
through their vendor(s).

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JudeD]

Yep, everything's set up correctly. This is a setup we've used for years in
this manner, but I've upgraded and am blazing the trail in the Access
2007/SQLServer 2005/Vista territory. Some tables work fine and some don't,
and there's no apparent logical reason. Of course the ones I really need
right now don't work. Probably will open an incident with Microsoft.
Thanks for your help!

 

[JudeD]

Problem was: Bigint datatype in a key field of database.