I am trying to create via a CREATE TABLE command (CurrentDB.Execute sql) a single table that links a "referee" from one table with a "team" from a team table.
I know how to do this manually via the Table design view, but I need to do this programmatically via VB code.
Table "Referee Data"
Primary Key Field Name: "ID"
Table "Teams"
Primary Key Field Name: "ID"
Here is the basic command that works correctly:
I am trying to set this up so that if the referee's record, from the table "Referee Data" is deleted, the corresponding records in TeamConflict2 are also deleted.
Also, if the team's record, from the table "Teams" is deleted, the corresponding records in TeamConflict2 are also deleted.
I have tried each of the following:
and
Each time I get a "syntax error in CONSTRAINT clause".
Any thoughts? What am I missing here?
----------
Jeff Wigal
Referee Assistant for MS Access
I know how to do this manually via the Table design view, but I need to do this programmatically via VB code.
Table "Referee Data"
Primary Key Field Name: "ID"
Table "Teams"
Primary Key Field Name: "ID"
Here is the basic command that works correctly:
Code:
CREATE TABLE TeamConflict2 (
RefereeID Long NOT NULL
CONSTRAINT FKReferee
REFERENCES [Referee Data] (ID),
TeamID Long NOT NULL
CONSTRAINT FKTeam
REFERENCES [Teams] (ID),
CONSTRAINT RefTeam
PRIMARY KEY (RefereeID, TeamID)
);
Also, if the team's record, from the table "Teams" is deleted, the corresponding records in TeamConflict2 are also deleted.
I have tried each of the following:
Code:
CREATE TABLE TeamConflict2 (
RefereeID Long NOT NULL
CONSTRAINT FKReferee
REFERENCES [Referee Data] (ID)
ON DELETE CASCADE,
TeamID Long NOT NULL
CONSTRAINT FKTeam
REFERENCES [Teams] (ID)
ON DELETE CASCADE,
CONSTRAINT RefTeam
PRIMARY KEY (RefereeID, TeamID)
);
and
Code:
CREATE TABLE TeamConflict2 (
RefereeID Long NOT NULL
CONSTRAINT FKReferee
REFERENCES [Referee Data] (ID),
TeamID Long NOT NULL
CONSTRAINT FKTeam
REFERENCES [Teams] (ID),
CONSTRAINT RefTeam
PRIMARY KEY (RefereeID, TeamID)
ON DELETE CASCADE
);
Each time I get a "syntax error in CONSTRAINT clause".
Any thoughts? What am I missing here?
----------
Jeff Wigal
Referee Assistant for MS Access