how to set defaut value in create table sql

  • Thread starter Thread starter mahongquan
  • Start date Start date
M

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


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.
 
mahongquan said:
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.
Click to expand...
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
 
Back
Top