Defining default values for fieds in CREATE TABLE statement?

[Thomas Malia]

How do you do the equivalent to the following in Access 2000? No matter
what I try I get a syntax error:



CREATE TABLE SQLProcs
(
RecID AUTOINCREMENT ,
Name VarChar(50) NOT NULL ,
SysOrAppDB VarChar(50) NOT NULL DEFAULT 'A', <--- I can't figure out how
to set a DEFAULT using SQL in access!?!?!
RebootDSLIfRebuilt Bit NOT NULL DEFAULT 0,
RebuildIfExists Bit NOT NULL DEFAULT 0,
CreateString TEXT NULL
)

 

[Michael Gramelspacher]

Sub CreateTable()

Dim s As String

s = "CREATE TABLE SQLProcs" & _
"(" & _
" RecID IDENTITY(1,1) NOT NULL," & _
" [Name] VARCHAR(50) NOT NULL," & _
" SysOrAppDB VARCHAR(50) DEFAULT A NOT NULL," & _
" RebootDSLIfRebuilt BIT DEFAULT 0 NOT NULL," & _
" RebuildIfExists BIT DEFAULT 0 NOT NULL," & _
" CreateString TEXT NULL" & _
");"

CurrentProject.Connection.Execute s
End Sub

 

[Thomas Malia]

I tried this syntax in the Query window in Access and it throws a syntax
error and leaves the word DEFAULT highlighted. I also tried the same syntax
from an ODBC connected generic database query tool and it throws a "Generic
SQL" error.

Sub CreateTable()

Dim s As String

s = "CREATE TABLE SQLProcs" & _
"(" & _
" RecID IDENTITY(1,1) NOT NULL," & _
" [Name] VARCHAR(50) NOT NULL," & _
" SysOrAppDB VARCHAR(50) DEFAULT A NOT NULL," & _
" RebootDSLIfRebuilt BIT DEFAULT 0 NOT NULL," & _
" RebuildIfExists BIT DEFAULT 0 NOT NULL," & _
" CreateString TEXT NULL" & _
");"

CurrentProject.Connection.Execute s
End Sub

CREATE TABLE SQLProcs
(
RecID AUTOINCREMENT ,
Name VarChar(50) NOT NULL ,
SysOrAppDB VarChar(50) NOT NULL DEFAULT 'A', <--- I can't figure out
how
to set a DEFAULT using SQL in access!?!?!
RebootDSLIfRebuilt Bit NOT NULL DEFAULT 0,
RebuildIfExists Bit NOT NULL DEFAULT 0,
CreateString TEXT NULL
)

 

[Michael Gramelspacher]

I am using Access 2003, and it works for me. I just created a new
database and added a new module and copied the code into the module,
and it works. Maybe try the same, unless you already did. Perhaps
someone else knows what the problem is.

 

[Thomas Malia]

I'm guessing it's an Access version issue then. I'm use Access 2000. Seems
likely that this feature may not have been supported in such an earlier
version.

Guess I'll live with the short coming for now. I'm already having fits with
AUTOINCRIMENT/IDENTITY anyway. In the "bigger picture" I'm trying to write
DDL scripts that will create equivalent database structures in MSSQL, MySQL
and Access... for many of the features I originally used in the design while
working in just one of these, the systems support and syntax is different
enough to make it next to impossible to support them all with one script.
So much for ANSI-standard.

 

[Allen Browne]

Access DDL really is inadequate. You can't set essential things like setting
the table's SubdatasheetName to [None], setting the Format of a number,
setting DisplayControl to acCheckbox for yes/no fields.

 

[RoyVidar]

I'm guessing it's an Access version issue then. I'm use Access 2000. Seems
likely that this feature may not have been supported in such an earlier
version.

No, the issue is that the code demonstrated by Gramelspacher, is Jet
SQL, not Access SQL. It won't work in the interface, it will only work
when executed on an ADO/OLE DB connection. Well, in later versions
(2002+), you can switch to ANSI-92 mode, and be able to execute it
also through the interface.

Copy/Paste Gramelspachers code into a module, and execute it, and you
will get a table (at least I got one using that code with Access
2000/VBA)

Guess I'll live with the short coming for now. I'm already having fits with
AUTOINCRIMENT/IDENTITY anyway. In the "bigger picture" I'm trying to write
DDL scripts that will create equivalent database structures in MSSQL, MySQL
and Access... for many of the features I originally used in the design while
working in just one of these, the systems support and syntax is different
enough to make it next to impossible to support them all with one script.
So much for ANSI-standard.

Jet SQL is much closer to ANSI 92 than the Access dialect, it also
supports a several interesting features that are not supported through
the Access interface (check constraints, is one of them - if you search
these groups on that term, you will probably both find some controverse,
but also some quite interesting samples).

If you wish to use DDL, then, as stated above, you'll need to execute on
an ADO/OLE DB connection.

Here's one article with a bit more information
http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx
(also check out the Fundamental and Advanced articles, linked to in
the bottom of the article)