CREATE TABLE: how to define a field's DEFAULT data value?

E

Ed B

Hi everyone, and thanks for all the good tips. This is an awesome community
for Access developers. My question:

Is it possible, within the "CREATE TABLE" SQL syntax in Access (Jet 3.5 or
4.0), to define a default value for a field?

I have seen that this feature is supported in SQLServer's CREATE TABLE
syntax, but either I am implementing it incorrectly (it's been a long time
since I had to intrepret a formal language definition ;-), or DEFAULT is not
implemented in the Access/Jet SQL CREATE TABLE statement.

Here's what the documentation says for SQL Server:

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > } [ ,...n ]
)

....

< column_definition > ::= column_name data_type
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]

....

If CREATE TABLE has a DEFAULT capability in Jet then, pray tell, how do it
work?

Thanks in advance,

Ed
 
A

Allen Browne

JET 4 (Access 2000 and later) does support placing a literal value such as 0
or "New York" into the Default Value of a column. I don't believe it can use
a function call (such as "=Date()").

You may need to execute the query under ADO to get this to work.

The following example shows most of the things you can do in DDL in JET 4.
(It is not a good example of normalization.) The HourlyFee column has the
Default Value.

Sub CreateTableDDL()
Dim cmd As New ADODB.Command
Dim strSql As String

cmd.ActiveConnection = CurrentProject.Connection

strSql = "CREATE TABLE tblContractor " & _
"(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "tblContractor created."
End Sub
 
E

Ed B

Thanks, Allen!

I was wondering if DDL via ADO might be the answer. Still, I am puzzled
(although maybe I shouldn't be) that this doesn't seem to work via
"DoCmd.RunSQL strSQL" for the same exact same strSQL. Why doesn't the db
engine see the same thing coming at it either way??? Mayhaps someone else
will post an answer why or why not...

Thanks again,

Ed

Allen Browne said:
JET 4 (Access 2000 and later) does support placing a literal value such as 0

snip!
 
A

Allen Browne

The reason is that Access itself uses DAO.
The "A" in DAO *is* Access.

For those of us who work in Access with JET tables, DAO is the obvious
library to choose, but unfortunately, MS did not update DAO fully to handle
all the new features in JET 4. That means you do have to know multiple
libraries to be able to do everything. If you need a comparision reference
for the JET data types, see:
Field type names (JET, DDL, DAO and ADOX)
at:
http://allenbrowne.com/ser-49.html

Unfortunately, no DDL query (whether executed under DAO or ADO) can set
properties such as AllowZeroLength, which is crucial to data integrity IMHO.
That fact alone means that I don't use CREATE TABLE in any context where the
data integrity matters. Microsoft has been *so* inconsistent with this
crucial property: the default behavior changes between versions, and also
between libraries. It can be explicitly set with DAO or ADOX, so that's the
only way to create tables. But ADOX can't set other basic properties, such
as setting the DisplayControl for a yes/no field to be a check box.

We are back to DAO as the only library suitable for creating tables in
Access. (DAO has limitations too, such as not being able to create Decimal
type fields, but given how buggy they are, I personally consider that one to
be an advantage of DAO.)
 
E

Ed B

Allen,

FANTASTIC! Thanks so much for taking the time to write such an accurate and
insightful reply.

IMO, your "... Microsoft has been *so* inconsistent..." point is most
relevant and cogent. There are always at least two or three ways to do just
about everything in the MS world but, depending on the situation, only one
might be "correct." The trick being, of course, how to be as aware as you
are of the tradeoffs. It's tough enough to know what's in all of the
libraries, much less developing a full understanding of the tradeoffs and
implications.

I look forward to browsing the other resources that you have created on your
site.

Thanks again,

Ed
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top