MS ACCESS sql problem

Question

MS ACCESS sql problem

suhaimiaj

Grasshopper

Points: 15
More actions
September 22, 2008 at 7:31 am

#71914

Cannot run below statement using MS Access sql design. Need help, the correct syntax.
create table Subject (
id CHAR(8) CONSTRAINT ValidClassID
CHECK (id LIKE 'COMP%'),
name VARCHAR(30),
PRIMARY KEY (id)
create table Class (
subject CHAR(8),
meetsAt VARCHAR(15),
room VARCHAR(15),
teacher NUMBER(6),
PRIMARY KEY (subject,meetsAt),
FOREIGN KEY (teacher) REFERENCES Faculty(staff#),
FOREIGN KEY (subject) REFERENCES Subject(id)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eric M Flamm SSChasing Mays Points: 641 More actions · Answer 1

Here's a Subroutine that will create your 2 tables, the primary keys, and foreign key relationships. I don't see how you can add the Validation Rule in the Create Table statement - but you can certainly add it with some DAO logic once the tables are created. I'll take a look at that logic shortly.

-EricF

Sub makeTables()

Dim strSQL As String

strSQL = "Create Table Subject2(id text(8) NOT NULL PRIMARY KEY ,name text(30))"

DoCmd.RunSQL (strSQL)

strSQL = "create table Class (subject text(8),meetsAt text(15),room text(15),teacher integer,"

strSQL = strSQL & " PRIMARY KEY (subject,meetsAt)"

strSQL = strSQL & ",CONSTRAINT TeacherFK FOREIGN KEY (teacher) REFERENCES Faculty(staffID)"

strSQL = strSQL & ",CONSTRAINT FKSubject FOREIGN KEY (subject) REFERENCES Subject(id)"

strSQL = strSQL & ")"

'Debug.Print strSQL

DoCmd.RunSQL (strSQL)

End Sub

[font="Tahoma"]Eric Flamm, Flamm Consulting[/font]

Eric M Flamm SSChasing Mays Points: 641 More actions · Answer 2

Here's the VBA to add the validation rule (with a little help from MSDN)

-Eric

Sub addValidationRule()

Dim strTblName As String, strFldName As String

Dim strValidRule As String

Dim strValidText As String, intX As Integer

strTblName = "Subject2"

strFldName = "id"

strValidRule = "like 'comp*'"

strValidText = "id is 8 characters or less, starting with comp"

intX = SetFieldValidation(strTblName, strFldName, strValidRule, strValidText)

End Sub

Function SetFieldValidation(strTblName As String, _

strFldName As String, strValidRule As String, _

strValidText As String) As Integer

'Courtesy of MSDN (http://msdn.microsoft.com/en-us/library/aa173474(office.11).aspx)

Dim dbs As Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb

Set tdf = dbs.TableDefs(strTblName)

Set fld = tdf.Fields(strFldName)

fld.ValidationRule = strValidRule

fld.ValidationText = strValidText

End Function

[font="Tahoma"]Eric Flamm, Flamm Consulting[/font]