By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,649 Members | 696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,649 IT Pros & Developers. It's quick & easy.

Access Defaults in Standard SQL

P: n/a
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.

Oct 21 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 21 Oct 2006 12:47:08 -0700, "mishj" <mi***@yahoo.comwrote:

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.
Oct 22 '06 #2

P: n/a
"mishj" <mi***@yahoo.comwrote in news:1161460027.904701.254170
@b28g2000cwb.googlegroups.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.Execute "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
Oct 22 '06 #3

P: n/a
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
Oct 22 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.