Access 2003 using VBA

5 pts.
Tags:
Microsoft Access
Microsoft Access 2003
RDBMS
VBA
I am trying really hard to produce these tables. But when it came to populating the information into them, it was throwing up errors. Ive managed to sort most of them out, but this one for WeeklyUnitStaffRota table is annoying me. Can anyone have a look at the tables and see where I could have gone wrong??
Option Compare Database
Public DB As New ADODB.Connection
Public RS As New ADODB.Recordset
Public Sub SetDB()
    Set DB = Application.CurrentProject.Connection
End Sub
Public Sub CreateDB()

DB.BeginTrans

If Not (TableExists("Medication")) Then
DB.Execute "CREATE TABLE Medication(MedicationID CHAR(10) CONSTRAINT PK_Medication PRIMARY KEY, Name CHAR(100) NOT NULL, Description CHAR(150) NOT NULL);"
End If

If Not (TableExists("Governing")) Then
DB.Execute "CREATE TABLE Governing(GoverningNo COUNTER, GoverningID CHAR(20), GoverningName CHAR(30), CONSTRAINT PK_Governing PRIMARY KEY(GoverningNo), GoverningAdd CHAR(75) NOT NULL, GoverningPhone CHAR(15));"
End If

If Not (TableExists("Referee")) Then
DB.Execute "CREATE TABLE Referee(RefereeID CHAR(45) CONSTRAINT PK_Referee PRIMARY KEY, RefCompany CHAR(30) NOT NULL, RefPhone CHAR(15));"
End If

If Not (TableExists("Unit")) Then
DB.Execute "CREATE TABLE Unit(UnitNo TINYINT CONSTRAINT PK_Unit PRIMARY KEY, Location CHAR(50) NOT NULL);"
End If

If Not (TableExists("Residents")) Then
DB.Execute "CREATE TABLE Residents(ResidentID CHAR(10) CONSTRAINT PK_Resident PRIMARY KEY, Forename CHAR(25) NOT NULL, Surname CHAR(50) NOT NULL, Sex TEXT(1) NOT NULL, DOB DATETIME NOT NULL, DateRegistered DATE NOT NULL, ResidentAddress CHAR(35), ResidentTown CHAR(20), KinForename CHAR(25) NOT NULL, KinSurname CHAR(50) NOT NULL, KinAddress CHAR(35) NOT NULL, KinTown CHAR(20) NOT NULL, KinPostcode CHAR(10) NOT NULL, KinPhone CHAR(15), KinRelation CHAR(15) NOT NULL, UnitNo TINYINT NOT NULL DEFAULT 0, RoomNo TINYINT NOT NULL DEFAULT 0, CONSTRAINT FK_Residents FOREIGN KEY (UnitNo) REFERENCES Unit, CONSTRAINT CK_P CHECK(SEX='M' or Sex='F'));"
End If

If Not (TableExists("Staff")) Then
DB.Execute "CREATE TABLE Staff(StaffNo CHAR(10) CONSTRAINT PK_Staff PRIMARY KEY, Forename CHAR(25) NOT NULL, Surname CHAR(50) NOT NULL, Address CHAR(35) NOT NULL, Town CHAR(20) NOT NULL, Postcode CHAR(10) NOT NULL, Sex TEXT(1) NOT NULL, DOB DATETIME NOT NULL, PhoneNo CHAR(15), NINo CHAR(15) NOT NULL, StaffPosition CHAR(50) NOT NULL, HoursWeek TINYINT NOT NULL, Salary CURRENCY, DateStarted DATETIME NOT NULL, CONSTRAINT CK_P2 CHECK(Sex='M' or Sex='F'));"
End If

If Not (TableExists("Qualifications")) Then
DB.Execute "CREATE TABLE Qualifications(QualificationID INTEGER CONSTRAINT PK_Qualifications PRIMARY KEY, NameQualification CHAR(75) NOT NULL, CONSTRAINT FK_Qualifications1 FOREIGN KEY (QualificationID) REFERENCES Governing(GoverningNo));"
End If

If Not (TableExists("StaffQualification")) Then
DB.Execute "CREATE TABLE StaffQualification(StaffQNo CHAR(10), SQualificationID INTEGER, StaffQDate DATETIME NOT NULL, CONSTRAINT PK_StaffQualification PRIMARY KEY(StaffQNo, SQualificationID), CONSTRAINT FK_Staff2 FOREIGN KEY(StaffQNo) REFERENCES Staff(StaffNo), CONSTRAINT FK_Qualifications2 FOREIGN KEY(SQualificationID) REFERENCES Qualifications(QualificationID));"
End If

If Not (TableExists("StaffPreviousJob")) Then
DB.Execute "CREATE TABLE StaffPreviousJob(StaffPJNo CHAR(10), CompanyID CHAR(20), StaffPJPosition CHAR(35) NOT NULL, CONSTRAINT PK_StaffPreviousJob PRIMARY KEY(StaffPJNo, CompanyID), CONSTRAINT FK_Staff3 FOREIGN KEY(StaffPJNo) REFERENCES Staff(StaffNo), CONSTRAINT FK_Reference1 FOREIGN KEY(CompanyID) REFERENCES Referee(RefereeID));"
End If

If Not (TableExists("MedicationRota")) Then
DB.Execute "CREATE TABLE MedicationRota(MedWeekBegin DATETIME, MedResidentsID CHAR(10), MedMedicationID CHAR(10), MedDosage INTEGER NOT NULL, MedUnitsPerDay INTEGER NOT NULL, CONSTRAINT PK_MedicationRota PRIMARY KEY(MedWeekBegin, MedResidentsID, MedMedicationID), CONSTRAINT FK_Residents1 FOREIGN KEY(MedResidentsID) REFERENCES Residents(ResidentID), CONSTRAINT FK_Medication1 FOREIGN KEY(MedMedicationID) REFERENCES Medication(MedicationID));"
End If

If Not (TableExists("WeeklyUnitStaffRota")) Then
DB.Execute "CREATE TABLE WeeklyUnitStaffRota(StaffRotaNo COUNTER, WeekBegin DATETIME NOT NULL, UnitNo TINYINT NOT NULL, StaffNo CHAR(10) NOT NULL, PK_WeeklyUnitStaffRota PRIMARY KEY(StaffRotaNo), CONSTRAINT FK_WeekBegin1 FOREIGN KEY(WeekBegin) REFERENCES MedicationRota(MedWeekBegin), CONSTRAINT FK_Unit1 FOREIGN KEY(UnitNo) REFERENCES Unit, CONSTRAINT FK_Staff1 FOREIGN KEY(StaffNo) REFERENCES Staff);"
End If

DB.CommitTrans

End Sub

Answer Wiki

Thanks. We'll let you know when a new response is added.

The ‘CONSTRAINT’ keyword is missing before the constraint name of the primary key (PK_WeeklyUnitStaffRota).

Please let us know if you get more errors.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following