• PHP
  • Ruby on Rails
  • MySQL
  • Linux
    • SELINUX
    • Fedora
    • debian
  • Apache
  • nginx
  • AJAX
Albertech.net

Fix: Editing tables in Access 2010 with Microsoft SQL Server 2008 R2 connection

February 19, 2013 5:28 pm / Albertech.net

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

 

 

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: SQL Server / Tagged: access 2010, sql server

Comments are closed.

Post Navigation

← Previous Post
Next Post →

Categories

  • AJAX
  • Android
  • Apache
  • Canon Cameras
  • Cloud
  • CMS
  • Computer Mods
  • Conferences
  • Deals
  • debian
  • Fedora
  • Flash
  • Frameworks
  • git
  • Hardware
  • HTML
  • IDE
  • iPhone
  • iPhone App Review
  • jQuery
  • Linux
  • Mac OS X
  • MySQL
  • nginx
  • PHP
  • portfolio
  • Puppet
  • Ruby on Rails
  • Script Reviews
  • SELINUX
  • Software
  • Software Review
  • SQL Server
  • statistics
  • Tech
  • Tomcat
  • Uncategorized
  • VMWARE
  • VPS
  • Windows
  • wordpress
  • Zend Framework

Blogroll

  • DragonAl Flickr
  • Dropbox – Free 2GB Account
  • James' Blog
  • Javascript Compressor
  • PHP Builder Community
  • PHP-Princess.net
  • Rubular – Regular Expression Validator
  • The Scale-Out Blog
  • Tiny MCE

Tags

activation AJAX android antec Apache AWS awstats canon coda codeigniter debian enclosure external free G1 install vmware tools Internet Explorer iphone 5 jquery Linux mx-1 MySQL office 2007 OSX photoshop PHP plugin plugins portfolio redesigned website review rewrite script security SELinux ssh tinymce tutorial upgrade VMWARE vmware server wordpress wordpress mu XSS zend framework
© Copyright 2013 Albertech.net
Infinity Theme by DesignCoral / WordPress
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.