ALTER TABLE ADD

[Bill]

In the segment of code below, I'm adding a 7 character field to the
Registry table. Where in the syntax of the ALTER TABLE string
you see being assigned to strDDL can I specify the default value?
And how, like Default(value)?

strDDL = "ALTER TABLE Registry ADD Column Baptized text(7)"
conBackend.Execute strDDL, dbFailOnError

Where conbackend is: Dim conBackend As ADODB.Connection

Thanks,
Bill

 

[Douglas J. Steele]

I haven't tested against Jet, but some dialects of DDL allow:

strDDL = "ALTER TABLE Registry ADD Column Baptized text(7) with default
'abcdefg' "
conBackend.Execute strDDL, dbFailOnError

 

[Bill]

Hi Doug,
With the addition of "with default 'Unknown'" Jet gave
me a syntax error. Didn't like "Default('Unknown')"
either.
Bill

 

[Douglas J. Steele]

You may have to use DAO, then. (Actually, using DAO against Jet is better
than using ADO anyhow.)

 

[Bill]

I would wholeheartedly agree. However, this is the
application you've assisted me with in the past and
for some reason mixing DAO and ADO within the
same application gave us fits the last time. It was in
this same general module that I'd converted over to
use DAO and the whole application seemed to go into
the tank. It was such a bag of snakes that I abandoned
that effort. And, ever since that experience I've used
DAO in all subsequent development activities where
there wouldn't be a mix.

So, I fear that I wouldn't be doing anything but going
down the same path with not much hope of success.

Maybe someone with ADO experience in this area
will chime in with an answer. In the meantime, I'll
just put some field initialization code in the OnOpen
of the only form that uses the new field.

Bill

 

[RoyVidar]

Hi Doug,
With the addition of "with default 'Unknown'" Jet gave
me a syntax error. Didn't like "Default('Unknown')"
either.
Bill

I don't think the single quotes, or the "With" keyword are necessary,
try without

....myField TEXT(7) NOT NULL DEFAULT Unknown"

and, I think it must be executed on an ADO/OLE DB connection.

More info on Check out
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

 

[Bill]

Thanks Roy, I finally found a few minutes to test your
suggestion. It does in fact set the correct default value
for newly created records based upon the column
add.

Thanks again,
Bill

 

[Jamie Collins]

Actually, using DAO against Jet is better
than using ADO anyhow.

That is your opinion and one I find surprising in the context of SQL
DDL because the ANSI-92 Query Mode DDL (being the one which must be
used via ADO) is much richer than ANSI-89 Query Mode DDL (being the
one which must be used via DAO) by any objective comparison.

Jamie.

--