By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,613 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

How to modify Primary Key?

P: n/a
Hi,

I am trying to modify the primary key of a table. We need to add
new field and make it part of the Primary key of the table.

I thought I can use this SQL Statement.

ALTER TABLE tablename DROP PRIMARY KEY

I got error "Syntax error in ALTER TABLE statement"

I use VB and ADO connection to execute the SQL statement.

Do I need to completely wipe out the table before I can modify it?

Please let me know if there is any other way to change primary key
by now going into the MS Access.

Thanks,
Deen

Apr 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I did some testing, it looks like the SQL statement that I used to
create primary key

"ALTER TABLE tablename ADD PRIMARY KEY (fields)" assigns the random
index name for it.

And the Access doesn't recognize key word PRIMARY.

There are many way to create primary key using SQL. Could you tell me
the best way?

I think I might have to drop the table and recreate it then put the
data back in for this time.

If you have any other advice please let me know.

-- Deen

On Apr 5, 4:42 pm, "Khundeen" <khund...@gmail.comwrote:
Hi,

I am trying to modify theprimarykeyof atable. We need to add
new field and make it part of thePrimarykeyof thetable.

I thought I can use this SQL Statement.

ALTERTABLEtablenameDROPPRIMARYKEY

I got error "Syntax error inALTERTABLEstatement"

I use VB and ADO connection to execute the SQL statement.

Do I need to completely wipe out thetablebefore I can modify it?

Please let me know if there is any other way to changeprimarykey
by now going into the MS Access.

Thanks,
Deen

Apr 6 '07 #2

P: n/a
Here's a couple of examples:
http://allenbrowne.com/func-DDL.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Khundeen" <kh******@gmail.comwrote in message
news:11**********************@b75g2000hsg.googlegr oups.com...
>I did some testing, it looks like the SQL statement that I used to
create primary key

"ALTER TABLE tablename ADD PRIMARY KEY (fields)" assigns the random
index name for it.

And the Access doesn't recognize key word PRIMARY.

There are many way to create primary key using SQL. Could you tell me
the best way?

I think I might have to drop the table and recreate it then put the
data back in for this time.

If you have any other advice please let me know.

-- Deen

On Apr 5, 4:42 pm, "Khundeen" <khund...@gmail.comwrote:
>Hi,

I am trying to modify theprimarykeyof atable. We need to add
new field and make it part of thePrimarykeyof thetable.

I thought I can use this SQL Statement.

ALTERTABLEtablenameDROPPRIMARYKEY

I got error "Syntax error inALTERTABLEstatement"

I use VB and ADO connection to execute the SQL statement.

Do I need to completely wipe out thetablebefore I can modify it?

Please let me know if there is any other way to changeprimarykey
by now going into the MS Access.

Thanks,
Deen
Apr 6 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.