Access Defaults in Standard SQL

[mishj]

Just want you all to know (just in case you have this issue) that some
standard sql doesnt work by default in MS ACCESS 2003 anymore (I didnt
know this, not sure if others do or not - and I couldnt find an answer
to my problem anywhere else on the internet)

I was getting a maddening syntax error on an alter statement that I
knew should work
ALTER TABLE [ARTICLE]
ADD COLUMN [APPROVED] CHAR(1) DEFAULT "N"

- it turns out its because 2003 defaults to "Access SQL" and you have
to switch it to Standard SQL syntax by doing the following..


Tools | Options | Tables/Queries | SQL Server Compatible Syntax (ANSI
92) |
<CheckThis database.

[Tom van Stiphout]

Re: Access Defaults in Standard SQL

On 21 Oct 2006 12:47:08 -0700, "mishj" <mishj@yahoo.co mwrote:

Wow, I would have missed this question on an exam :-)

I wonder if it's a good idea to set this flag, or if it would create
other problems. The warning message is rather severe. I would only do
this on a NEW database, not on an existing app.

There is an Access way of adding a field: see the CreateField function
in the Help file.

-Tom.

>Just want you all to know (just in case you have this issue) that some
>standard sql doesnt work by default in MS ACCESS 2003 anymore (I didnt
>know this, not sure if others do or not - and I couldnt find an answer
>to my problem anywhere else on the internet)
>
>I was getting a maddening syntax error on an alter statement that I
>knew should work
>ALTER TABLE [ARTICLE]
>ADD COLUMN [APPROVED] CHAR(1) DEFAULT "N"
>
>- it turns out its because 2003 defaults to "Access SQL" and you have
>to switch it to Standard SQL syntax by doing the following..
>
>
>Tools | Options | Tables/Queries | SQL Server Compatible Syntax (ANSI
>92) |
><CheckThis database.

[Lyle Fairfield]

Re: Access Defaults in Standard SQL

"mishj" <mishj@yahoo.co mwrote in news:1161460027 .904701.254170
@b28g2000cwb.go oglegroups.com:

Just want you all to know (just in case you have this issue) that some
standard sql doesnt work by default in MS ACCESS 2003 anymore (I didnt
know this, not sure if others do or not - and I couldnt find an answer
to my problem anywhere else on the internet)
>
I was getting a maddening syntax error on an alter statement that I
knew should work
ALTER TABLE [ARTICLE]
ADD COLUMN [APPROVED] CHAR(1) DEFAULT "N"
>
- it turns out its because 2003 defaults to "Access SQL" and you have
to switch it to Standard SQL syntax by doing the following..
>
Tools | Options | Tables/Queries | SQL Server Compatible Syntax (ANSI
92) |
<CheckThis database.

Do you think the JET of pre-2003 versions of Access uses Ansi 92 SQL by
default?

In any case instead of making the option change you recommend which can
influence all your SQL it may be less hazardous just to use OLE DB as
manifested in ADO as:

CurrentProject. Connection.Exec ute "ALTER TABLE [ARTICLE] ADD COLUMN
[APPROVED] CHAR(1) DEFAULT 'N'"

or to rewrite the SQL in a fashion that JET SQL can handle.

Persons who have Ansi 92 SQL experience, skill and knowledge might want to
make the change you suggest. There may be half-a-dozen who post here who
would be safe in doing so.

--
Lyle Fairfield

[Tim Marshall]

Re: Access Defaults in Standard SQL

Lyle Fairfield wrote:

Persons who have Ansi 92 SQL experience, skill and knowledge might want to
make the change you suggest. There may be half-a-dozen who post here who
would be safe in doing so.

ANSI JOINS (how Jet constructs joins, BTW) BLOW!!!!! Who the hell ever
came up with such a stupid way to make joins deserves to be buried in
parenthesis. Viva Oracle's simple theta join in this regard!!!!

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me