I have been unsuccessful at finding someone elsewhere who can answer this question for me, so I'll try here. I need to create a many-to-many relationship in an Access 2000 (or '97 if not possible in 2000) DB, using VB code or SQL. I would prefer to do this using ADOX, but at this point, I'll take anything that works. So far, I have been unsuccessful at doing this, and receive errors that "there is no unique index in the primary table" when I try to create a many-to-many relationship. The need to do so is mostly a result of poor DB design by someone before my time, but at this point I can't change the DB format.
I'm sure that there must be some way to do this because Access will allow you to create a many to many relationship in the Relationships design screen (Tools, Relationships) by unchecking the "Enforce Referential Integrity" box. I just need to figure out how to do this in code.
That would certainly work, and I've already thought of that option, but unfortunately I cannot change the format of the database right now. I fact, if I could, I would redesign it so that it would have the proper primary keys and be normalized, thereby eliminating the problem altogether. Any other ideas?
In actuality, I am trying to create a structural copy of a current database implementation in code. I can do everything I need to except create the many-to-many relationships. I am not at liberty to "change" the format of the database by adding new tables. I simply want to be able to structurally create the same database programatically. So what I "want to do," is as I said before, create a many-to-many relationship.