How to drop a primary key?

[acjeff]

By using VB6 to work with Access database, I am trying to delete a field with primary key in a table then I will set another primary key in another field.

db.Execute "ALTER TABLE tblSealTests DROP CONSTRAINT ID"
error: CHECK constraint 'ID' does not exist

Note that the table, field and primary key was created in Access instead of VB code.

 

[acjeff]

One more question: To avoid error, how to check if the primary key is set in the ID field before I remove it?

 

[petrusp]

You want to drop the constraint, not the field. The constraint has a unique name, different from the field name (Something like 'pk_id'). So you will have to know the constraint name first.

"ALTER TABLE Table DROP CONSTRIANT pk_id

 

[petrusp]

I'd suggest posting this in a SQL forum.

 

[acjeff]

How can I know what the constraint name is? Again, the table was created in Access instead of by VB code.

 

[BobRodes]

Well, the other thing you could do is populate a new table with whatever you want, drop the old table, and then add a primary key to the new table.

Bob

 

[acjeff]

Yes Bob, I am trying to do the way you advised. However, here comes another error. After dropping the old table, when I rename the new table using

db.Execute "RENAME TABLE tempTable TO myTable"

I got this error: Microsoft Jet engine cannot find the input or query ...

So what's next?

 

[BobRodes]

How about

Select myTable INTO myTempTable
Drop myTable
Select myTempTable INTO myTable
drop myTempTable


? The sledgehammer approach is sometimes the simplest.

HTH

Bob