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