Error executing sql statement

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

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

Ivan Debono said:
What happens to the default? Doesn't JetSQL support it?

Ivan

Dave Patrick said:
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
Click to expand...
Click to expand...
 
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

Allen Browne said:
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.

Ivan Debono said:
What happens to the default? Doesn't JetSQL support it?

Ivan

Dave Patrick said:
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
Click to expand...
Click to expand...
Click to expand...
 
Ivan said:
Actually it does, but only from Jet 5.0
Click to expand...

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

--
 
Back
Top