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:7/29/09 11:47 AM


Details


Writing 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 Hemesh on 2/4/10 9:46 AM

Thanx A lot .... It helped me a lot !!!

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? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com