 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / August 2006Delete table relationship
Hi, I've three tables in many-to-many relationship (in a MDB db): TBL1: ID_TBL1 IDENTITY PRIMARY KEY FIELD1 VARCHAR(20) TBL_LINK: ID_TBL1 INTEGER ID_TBL2 VARCHAR (5) TBL2: ID_TBL2 VARCHAR(5) PRIMARY KEY FIELDA TIMESTAMP Now, I must add a field ID_RCD to TBL2 and make it a PRIMARY KEY, so: 1) Delete the ForeignKey from the link table ALTER TABLE TBL_LINK DROP CONSTRAINT ID_TBL2 2) Delete the PrimaryKey from TBL2: ALTER TABLE TBL2 DROP CONSTRAINT ID_TBL2 But here ADO raises the error (more or less): "Impossible delete the index or the table. Current Index or used in a relation" But..Why?! The only constraint on that field is the FK that I've deleted in 1) !!! Any suggestions? Daniele. ¤ Hi, ¤ I've three tables in many-to-many relationship (in a MDB db): ¤ ¤ TBL1: ¤ ID_TBL1 IDENTITY PRIMARY KEY ¤ FIELD1 VARCHAR(20) ¤ ¤ TBL_LINK: ¤ ID_TBL1 INTEGER ¤ ID_TBL2 VARCHAR (5) ¤ ¤ TBL2: ¤ ID_TBL2 VARCHAR(5) PRIMARY KEY ¤ FIELDA TIMESTAMP ¤ ¤ Now, I must add a field ID_RCD to TBL2 and make it a PRIMARY KEY, so: ¤ ¤ 1) Delete the ForeignKey from the link table ¤ ALTER TABLE TBL_LINK DROP CONSTRAINT ID_TBL2 ¤ ¤ 2) Delete the PrimaryKey from TBL2: ¤ ALTER TABLE TBL2 DROP CONSTRAINT ID_TBL2 ¤ ¤ But here ADO raises the error (more or less): ¤ "Impossible delete the index or the table. Current Index or used in a ¤ relation" ¤ ¤ But..Why?! The only constraint on that field is the FK that I've ¤ deleted in 1) !!! What are the actual names of your Constraints? It looks like you're dropping Columns and not Constraints. Paul ~~~~ Microsoft MVP (Visual Basic) Paul Clement ha scritto: > What are the actual names of your Constraints? It looks like you're dropping Columns and not Constraints. I think Access assign an internal name to the constrain, I have always used this statement to delete constraint. :-( Daniele. ¤ ¤ Paul Clement ha scritto: ¤ ¤ > What are the actual names of your Constraints? It looks like you're dropping Columns and not Constraints. ¤ > ¤ ¤ I think Access assign an internal name to the constrain, I have always ¤ used this statement to delete constraint. :-( ¤ ¤ Daniele. You may want to look in the MSysRelationships system table to see if you can find the name. Paul ~~~~ Microsoft MVP (Visual Basic) Paul Clement ha scritto: > You may want to look in the MSysRelationships system table to see if you can find the name. Great!! I had not thought to system tables!!! Great idea! Now I try!! Tnx a lot! Daniele. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.