Error executing sql statement

[Ivan Debono]

Hi,

I'm executing the following SQL statement using ado 2.8:

ALTER TABLE mytable ADD myfield text DEFAULT('')

And get the following error:
Microsoft JET Database Engine (3320) Missing ), ], or Item in table-level
validation expression.

I don't see anything missing. What the problem then?

Thanks,
Ivan

 

[Dave Patrick]

Try;

ALTER TABLE mytable ADD myfield text(x)

(where x = the string length)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
|
| I'm executing the following SQL statement using ado 2.8:
|
| ALTER TABLE mytable ADD myfield text DEFAULT('')
|
| And get the following error:
| Microsoft JET Database Engine (3320) Missing ), ], or Item in table-level
| validation expression.
|
| I don't see anything missing. What the problem then?
|
| Thanks,
| Ivan
|
|

 

[Ivan Debono]

What happens to the default? Doesn't JetSQL support it?

Ivan

 

[Allen Browne]

Ivan, if memory serves correctly, JET does not accept DEFAULT in DDL queries
executed under DAO (which is how the Access interface works), but it does
accept it if the DDL is executed under ADO.

Try:
Dim strSql As String
strSql = "ALTER TABLE MyTable ADD COLUMN MyZLSfield TEXT (100) DEFAULT
"""";"
CurrentProject.Connection.Execute strSql


Naturally, this requires the field's Allow Zero Length property is set to
Yes. This property is set inconsistently in different versions of Access and
depending on how the field is created. In general you want this property set
to No, but perhaps you have a special case.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What happens to the default? Doesn't JetSQL support it?

Ivan

Try;

ALTER TABLE mytable ADD myfield text(x)

(where x = the string length)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
|
| I'm executing the following SQL statement using ado 2.8:
|
| ALTER TABLE mytable ADD myfield text DEFAULT('')
|
| And get the following error:
| Microsoft JET Database Engine (3320) Missing ), ], or Item in
table-level
| validation expression.
|
| I don't see anything missing. What the problem then?
|
| Thanks,
| Ivan

 

[Ivan Debono]

Actually it does, but only from Jet 5.0 but the syntax is:
DEFAULT 0 instead of DEFAULT(0)
DEFAULT "hello" instead of DEFAULT('hello')

Ivan

Ivan, if memory serves correctly, JET does not accept DEFAULT in DDL
queries executed under DAO (which is how the Access interface works), but
it does accept it if the DDL is executed under ADO.

Try:
Dim strSql As String
strSql = "ALTER TABLE MyTable ADD COLUMN MyZLSfield TEXT (100) DEFAULT
"""";"
CurrentProject.Connection.Execute strSql


Naturally, this requires the field's Allow Zero Length property is set to
Yes. This property is set inconsistently in different versions of Access
and depending on how the field is created. In general you want this
property set to No, but perhaps you have a special case.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What happens to the default? Doesn't JetSQL support it?

Ivan

Try;

ALTER TABLE mytable ADD myfield text(x)

(where x = the string length)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
|
| I'm executing the following SQL statement using ado 2.8:
|
| ALTER TABLE mytable ADD myfield text DEFAULT('')
|
| And get the following error:
| Microsoft JET Database Engine (3320) Missing ), ], or Item in
table-level
| validation expression.
|
| I don't see anything missing. What the problem then?
|
| Thanks,
| Ivan

 

[Jamie Collins]

Actually it does, but only from Jet 5.0

<tongue firmly embedded in cheek> Wow, when did that come out? I've
been waiting for five years!

....or perhaps you meant Jet 4.0?

On a more serious note, I've never known an empty string to be at all
useful in a text column, let alone to be the default. I usually do the
complete opposite of the OP i.e. on the rare occasion where there is no
pattern matching or other validation to preclude one, I'll always
explicitly check to exclude zero-length strings e.g.

CREATE TABLE Blahs (...
description VARCHAR (200) DEFAULT '{{NONE}}' NOT NULL,
CONSTRAINT blah_description__not_zero_length
CHECK (LEN(description) > 0),
CONSTRAINT blah_description__curly_brackets_in_default_only
CHECK (description NOT LIKE '%[{}]%' OR description = '{{NONE}}'),
....

Jamie.

--