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 |
|---|
Details
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.
