how to set defaut value in create table sql

[mahongquan]

in my application,i use sql to communicate with access database,i don't know
how to set default value in create table syntax.who can help me? thanks a
lot.

 

[Marshall Barton]

in my application,i use sql to communicate with access database,i don't know
how to set default value in create table syntax.who can help me? thanks a
lot.

The DefaultValue property is not an inherant property in
Jet, it's one of the several that Access creates for you
when you specify it in the table design UI. When using DAO
to create a TableDef, you need to create the DefaultValue
property if it doesn't already exist.

Here's a code snipit to get you started:

Set fld = tdf.CreateField("MyField", dbLong)
tdf.Fields.Append fld ' Must append field before
creating its properties
Set prp = fld.CreateProperty("Caption", dbText, "Some
String")
fld.Properties.Append prp
fld.Properties.Refresh
tdf.Fields.Refresh

If there's a chance the property might already exist, use
error handling to catch it.

 

[Gary Walter]

in my application,i use sql to communicate with access database,i don't
know
how to set default value in create table syntax.who can help me? thanks a
lot.

Hi mahongquan,

"In my application..." can mean so many different things, but ...

In addition to Marshall's learned DAO advice,
here be some examples using CurrentProject.Connection.Execute ...

CurrentProject.Connection.Execute "CREATE TABLE z1 (myText TEXT(50) DEFAULT
""my Default value"" ) ", dbFailOnError
Currentproject.Connection.Execute "CREATE TABLE TEMP (Field1 single DEFAULT
0, Field2 long)", dbFailOnError
CurrentProject.Connection.Execute "CREATE TABLE TableName (FieldName LONG
DEFAULT 333);", dbFailOnError
Currentproject.Connection.Execute "CREATE TABLE test (DateField DATE DEFAULT
Date())", dbFailOnError

Currentproject.Connection.Execute "ALTER TABLE Temp ALTER COLUMN Field2 SET
DEFAULT 5", dbFailOnError

Apologies for jumping in.

good luck,

gary