Create table statement syntax error in access 2000

[tony007]

Hi guys i tried to run this create table statement and each time i get syntax error.
I pasted the code in sql view windows of access 2000 and pressed the run code and i get
this error massage saying there is syntax error. Could any one help me write correct
create table statement that does not give me this error.I know u might tell me why u
do not create table in design view or .. but i want to do this since i want learn this
method as well.thanks

CREATE TABLE PLAYERS
(PLAYERNO SMALLINT NOT NULL  CHECK (PLAYERNO >0),
NAME CHAR(25) NOT NULL ,
INITIALS CHAR(5) NOT NULL ,
BIRTH_DATE DATETIME,
SEX CHAR(1) NOT NULL ,
JOINED SMALLINT CHECK (JOINED >=1980),
STREET CHAR(15) NOT NULL ,
HOUSENO CHAR(4),
POSTCODE CHAR(6),
TOWN CHAR(10) NOT NULL ,
PHONENO CHAR(10),
LEAGUENO CHAR(4),
PRIMARY KEY (PLAYERNO)
)

 

[raskew]

Tony -

Sitting with only A97 available at the moment, but suspect you're bombing with the Check statements. Unless there was a vast upgrade in the Create Table statement between A97 and A2000 -- which I doubt -- would have to say that there's no
provision to modify Data Validation rules via the Create Table statement. If I'm wrong,
please set me straight.

If called from the immediate window with:
call tabletest("Players")
...the following will DELETE ANY TABLE NAMED "PLAYERS" FROM YOUR DATABASE
AND RECREATE IT, MINUS DATA. THIS COULD POSSIBLY CAUSE A DISASTER IN
YOUR APPLICATION. PLEASE BACKUP YOUR DATA BEFORE TESTING THIS PROCEDURE!!

...the recreated table will include 'Required' for any field you specified as Not Null. It will not include validation rules, e.g., CHECK (PLAYERNO >0) and CHECK (JOINED >=1980).

If you have available A97, there are adequate help-file examples to show you how to update the Validation Rules property. A2000 = ????.

Sub tabletest(pTable As String)
'*******************************************
'Purpose:   Delete/recreate table programmaically
'from:      raskew
'*******************************************

Dim strSQL As String

    On Error Resume Next
    'WARNING: Table pTable will be deleted,
    '         and recreated.  If this is
    '         a problem, choose another,
    '         unique table name.
    CurrentDb.Execute "DROP TABLE " & pTable & ";"
    
    strSQL = "CREATE TABLE " & pTable & " " _
       & "( PlayerNo Integer Not Null" _
       & ", LastName Text(25) Not Null" _
       & ", Initials Text(5) Not Null" _
       & ", DOB      DateTime" _
       & ", Sex      Text(1) Not Null" _
       & ", Joined   Integer" _
       & ", Street   Text(15) Not Null" _
       & ", HouseNo  Text(4)" _
       & ", Postcode Text(6)" _
       & ", Town     Text(10) Not Null" _
       & ", PhoneNo  Text(10)" _
       & ", LeagueNo Text(4)" _
       & " )"
    CurrentDb.Execute strSQL
End Sub

hth - craigo