ALTER TABLE Change Primary Key Field?

[astralaaron]

Is there any way in SQL to change which field is the primary key? Then also drop the field from having a primary key?

 

Thanks in advance.

 

I should probably add I am using Access database at the moment...

DROP INDEX indexFIeld ON Table1

doesn't seem to work

Edited June 29, 2013 by astralaaron

[justsomeguy]

Here's the syntax:

 

http://msdn.microsoft.com/en-us/library/office/bb177897(v=office.12).aspx

 

Note that the index name is not necessarily the field name.