Set Primary Key constraint while creating table through code
I am creating a table in access database (.mdb) through VB
But I am not able to set the primary key constraint through code.
I searched on the forum regarding this...but no luck.
Here's the code I am using to create the table
Code:
Dim cn as New ADODB.Connection
cn.Open = "DSN=MyDSN"
sql = "create table Master(Id Number, Name Text)
cn.Execute sql
With the above code I am creating a table. But I want to set Id as my primary key.
How do I do this?
Re: Set Primary Key constraint while creating table through code
Re: Set Primary Key constraint while creating table through code
Here is some full syntax on CREATE TABLE
Code:
CREATE TABLE Event_T
(Bldg int NOT NULL
,Yr int NOT NULL
,ApptType int NOT NULL
,StartTime varchar(4) NOT NULL
,EndTime varchar(4) NOT NULL
,Teacher int NOT NULL
,Room varchar(10) NOT NULL
,ApptNotes varchar(50) NOT NULL
,TDate datetime NULL
,constraint PKEvent
PRIMARY KEY CLUSTERED (Bldg, Yr, ApptType, StartTime)
)
GO
ALTER TABLE Event_T
ADD CONSTRAINT FKApptTypeEvent
FOREIGN KEY (ApptType) REFERENCES ApptType_T(ApptType)
GO
And altering for a foreign key as well.
Re: Set Primary Key constraint while creating table through code
Ok thanks, will give it a try...
But will this work for MS Access.
Re: Set Primary Key constraint while creating table through code
The syntax might be slightly different - but the HELP FILE for ACCESS should tell you all that.
Re: Set Primary Key constraint while creating table through code
mcnn is the ADODB connection for access database
mcnn.execute "create table temp (id varchar(10) primary key)"
or
mcnn.execute "create table temp (id autoincrement primary key)"