WinSQL » Knowledge base

Document information

Document ID:758
Subject:Writing default values in Create Table statement
Creation date:7/29/09 11:47 AM
Last modified on:12/12/18 10:54 AM


Writing the following query in MS Access produces an error.

create table tbOne(
    id integer primary key,
    optionString char(1) default 'Y'

Error: Syntax error in CREATE TABLE statement. (State:42000, Native Code: FFFFF221)

Even though the above script seems like a valid SQL, Microsoft Access does not like it. This is because by default MS Access does not Extended ANSI SQL. To enable this option, follow the steps below.
  • Open ODBC Manager (either from Windows Control Panel or WinSQL's connection window.
  • Select your DSN for MS Access and click Configure button
  • Click the Advanced... button
  • From the Option list, select ExtendedANSISQL
  • Change its value from 0 to 1
Once this change has been made, you should be able to write the above SQL query without any errors.

User comments

Posted by WL on 7/29/09 12:11 PM

In Access 2003 you can set ANSI92 for a particular database by clicking on Tools > Tables/Queries > check box in lower right corder for ANSI92. Note the warning message that come up and the associated help page ("About ANSI SQL query mode (MDB)") are indeed helpful and important.

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our public forums to post questions.


Social Media

Powered by