Board index » delphi » Creating primary key in access

Jon Purvis

Delphi Developer
Sun, 19 Mar 2006 05:10:18 GMT

Creating primary key in access

Delphi 7 Pro, Access XP

In TableParticipants, the primary key currently is MemberID,Event.  I need to
change the key to MemberID,Event,EventDate.  I need to do this at the program
start when it checks for current database version.  I figured a simple drop key
and add key SQL statement would do the trick, but can't get the syntax right.
Any help correcting my code would be appreciated.

with QueryChange do begin
   {this portion works}
   Close;
   SQL.Clear;
   SQL.Add('alter table Participants drop PrimaryKey');
   ExecSQL;

   {this portion doesn't}
   Close;
   SQL.Clear;
   SQL.Add('alter table Participants add PrimaryKey (MemberID,Event,EventDate)');
   ExecSQL;
end;

Jon

 
Andy

Delphi Developer
Sun, 19 Mar 2006 09:10:03 GMT

Re:Creating primary key in access


try this (untested)

SQL.Add('alter table Participants add constraint PrimaryKey  Primary Key
(MemberID,Event,EventDate)');

Andy

Quote
"Jon Purvis" <jon.pur...@tpwd.state.tx.us> wrote in message

news:3f79f148@newsgroups.borland.com...
Quote
> Delphi 7 Pro, Access XP

> In TableParticipants, the primary key currently is MemberID,Event.  I need
to
> change the key to MemberID,Event,EventDate.  I need to do this at the
program
> start when it checks for current database version.  I figured a simple
drop key
> and add key SQL statement would do the trick, but can't get the syntax
right.
> Any help correcting my code would be appreciated.

> with QueryChange do begin
>    {this portion works}
>    Close;
>    SQL.Clear;
>    SQL.Add('alter table Participants drop PrimaryKey');
>    ExecSQL;

>    {this portion doesn't}
>    Close;
>    SQL.Clear;
>    SQL.Add('alter table Participants add PrimaryKey

(MemberID,Event,EventDate)');

- Show quoted text -

Quote
>    ExecSQL;
> end;

> Jon

Jon Purvi

Delphi Developer
Mon, 20 Mar 2006 00:00:44 GMT

Re:Creating primary key in access


That did the trick. Thanks.
Quote
Andy wrote:
> try this (untested)

> SQL.Add('alter table Participants add constraint PrimaryKey  Primary Key
> (MemberID,Event,EventDate)');

> Andy

> "Jon Purvis" <jon.pur...@tpwd.state.tx.us> wrote in message
> news:3f79f148@newsgroups.borland.com...

>>Delphi 7 Pro, Access XP

>>In TableParticipants, the primary key currently is MemberID,Event.  I need to
>>change the key to MemberID,Event,EventDate.  I need to do this at the program
>>start when it checks for current database version.  I figured a simple drop key
>>and add key SQL statement would do the trick, but can't get the syntax right.
>>Any help correcting my code would be appreciated.

>>with QueryChange do begin
>>   {this portion works}
>>   Close;
>>   SQL.Clear;
>>   SQL.Add('alter table Participants drop PrimaryKey');
>>   ExecSQL;

>>   {this portion doesn't}
>>   Close;
>>   SQL.Clear;
>>   SQL.Add('alter table Participants add PrimaryKey
>>    (MemberID,Event,EventDate)');
>>   ExecSQL;
>>end;

>>Jon

Other Threads

1. Creating primary key for Access 97 tables

2. Create primary key dynamic using SQL (I use access databas)

3. Create Access Tables with Primary Keys

4. ADOX, Creating a Primary Key for Access 2000 DB

5. ADOX: Creating a Primary Key for an Access 2000 DB

6. Primary key or not primary key.........

7. Primary key when creating Interbase table

8. Newbie: Create primary key with ADOX

9. primary key on create table?

10. Creating Primary Key on existing Interbase table