How to delete a relationship in Access in VBA 1

[lxn]

Hi, all.
Okay, this is what I'm doing. I was asked to write some code to make some changes to the structure of an existing database. Right now, I need to remove the primary key on one of the tables,(which can be done using the &quot;ALTER TABLE <tablename> DROP CONSTRAINT <PrimaryKey>&quot; statement) But the problem is that this field is used in a relationship connecting to another table. So to drop the primary key, I must first delete the relationship associated with it. And that's where I'm stuck. I know that it can be easily done manually in Acces, just by selecting it in the relationship window and deleting it, but I have no idea how to do that in VBA. I tried to use the &quot;ALTER TABLE <tablename> DROP CONSTRAINT <indexname>&quot; statement, but it doesn't seem to work, well probablly because it's wrong.
So, please, if anyone has done this before or knows where I can get more help, PLEASE HELP ME?!!!?

Thanks a lot.

 

[hsitz]

You will need to manipulate the Relations collection. You can search for some help on this in the Access VBA help system.

There is a delete method associated with the Relations collection, which needs to have the name of the relation object you're deleting specified.

You will need to find out what the name (or ordinal number) of the relation you want to delete is, then issue a command (I think) like:

currentdb.relations.delete &quot;[nameofrelation]&quot;

The Access help has an example that can help you determine what the existing relations' names are.

Sorry I'm not being of more help. I haven't ever actually done this, but have been interested in the question for a while and want to be prepared to tackle it whenever I do need to do it. Would be interested in hearing from you if you get something to work. -- Herb

 

[lxn]

Hey, Herb
Thanks for the help, I'm researching on that and trying to get that to work right now, I'll be sure to let you know if it works out.
Thanks again

Merlin

 

[lxn]

WOOHOOO!!! Yes, it works!
um, hey, Herb. I took your suggestion and used the delete method, and it works out perfectly. So, um, I just wanted to let you know. Thanks a lot for your help.
Basically I just added one line : iobjDB.Relations.Delete(<relationName>). I didn't find the example about how to find the relations' names though. Luckily, I found a SQL statement in the Microsoft: Access Tables and Relationships Forum, which displays the Relation Names of any table existing in the database:
SELECT MSysRelationships.szRelationship
FROM MSysRelationships
WHERE (((MSysRelationships.szReferencedObject)=[Table
Name?]));
so, thanks to those people too.

I haven't completely figured out how all the methods of the Relations Collection work yet, but I got what I wanted now, I'll just learn the rest of them later.

Again thanks to all you people who offered help, you guys rock!!!

Merlin