On a recent migration from Access 2010 to SQL Server 2008 R2, a client was having the following issue when inserting a row in the middle of a table or modifying the field size within the Design View. They were connecting to the SQL Server via a ADP file.
Unable to modify table.
ADO error: Incorrect syntax near ‘TABLOCKX’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
This is due to Microsoft changing the syntax for the ALTER statement in SQL Server 2008 to require a WITH keyword. Access 2010 does not generate SQL statements with the “WITH” keyword.
DISCLAIMER: This is for informational purposes only. We are providing absolutely no warranty for this information. Use this information at your own risk. Always make a copy of the database and make the changes on the test database first.
Solution: On the SQL Server 2008 R2 SSMS (SQL Server Management Studio), set the database compatibility to “SQL Server 2000 (80)“. Right click on the database, click on “Properties”, select the “Options” page. There should be a dropdown menu to select the “Compatibility level”. Change it from SQL Server 2008 to 2000. This allows older SQL commands to be used with the SQL server. The main difference with changing compatibility from 2008 to 2000 is the way the SQL optimizer processes the queries.
Reference: http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/70836032-f1d1-4ce9-9b38-86982941aec6
