Error establishing a relation in access database through sql statement

[worldperseus]

Hi
I Execute the following sql statement successfully for a Access Database.

CREATE TABLE Scores ( 
	StudentCode Text(9) NOT NULL,
	CourseCode Text(7) NOT NULL,
	SemesterCode Text(3) NOT NULL,
	Score Double
);

CREATE TABLE Courses ( 
	CourseCode Text(7) NOT NULL,
	CourseName Memo NOT NULL,
	CourseTypeID Integer NOT NULL,
	CourseCategoryID Integer NOT NULL
);

ALTER TABLE Scores ADD CONSTRAINT PK_Grades 
	PRIMARY KEY (StudentCode, CourseCode, SemesterCode);

ALTER TABLE Courses ADD CONSTRAINT PK_Courses 
	PRIMARY KEY (CourseCode);

ALTER TABLE Courses
	ADD CONSTRAINT UQ_Courses_CourseCode UNIQUE (CourseCode);

But Finally I Get Error message(Syntax error in CONSTRAINT clause.) on this satement that is at the end of my execution list.

ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses 
	FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode)
ON UPDATE CASCADE;

And I don't know Why?
Please Help me on how to establish a relation in access database through sql statement?

 

[MarkK]

DAO does not support the ON UPDATE clause, but you can execute the commands on an ADO connection as follows ...

[FONT="Tahoma"]Sub ExecuteCommandsOnADOConnection()
   Dim var
   For Each var In CommandArray
      [B]CurrentProject.Connection[/B].Execute var
   Next
End Sub

[SIZE="1"]Property Get CommandArray() As Variant
   CommandArray = Array( _
   "CREATE TABLE Scores ( " & _
      "StudentCode Text(9) NOT NULL, " & _
      "CourseCode Text(7) NOT NULL, " & _
      "SemesterCode Text(3) NOT NULL, " & _
      "Score Double );", _
   "CREATE TABLE Courses ( " & _
      "CourseCode Text(7) NOT NULL, " & _
      "CourseName Memo NOT NULL, " & _
      "CourseTypeID Integer NOT NULL, " & _
      "CourseCategoryID Integer NOT NULL );", _
   "ALTER TABLE Scores ADD CONSTRAINT PK_Grades " & _
      "PRIMARY KEY (StudentCode, CourseCode, SemesterCode);", _
   "ALTER TABLE Courses ADD CONSTRAINT PK_Courses " & _
      "PRIMARY KEY (CourseCode);", _
   "ALTER TABLE Courses " & _
      "ADD CONSTRAINT UQ_Courses_CourseCode UNIQUE (CourseCode);", _
   "ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses " & _
      "FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode) " & _
      "ON UPDATE CASCADE;")
End Property[/SIZE][/FONT]