Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Provo, UT, USA
    Posts
    3

    Unanswered: Many-to-many Relationships

    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.

    Anybody have any ideas?

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Have a look at the following example.

    Use a junction table as described in the example

    http://www.gj.thorpe.btinternet.co.u...y_to_many.html

    Hope this helps
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Aug 2003
    Location
    Provo, UT, USA
    Posts
    3

    That would work, but...

    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you "cannot change the format of the database," could you please describe what you want to do? adding a relationship is changing the database

    access does accept DDL (data definition language) to create primary and foreign keys, which you can submit via odbc or whatever -- but i gather this is not what you want?

    rudy
    http://r937.com/

  5. #5
    Join Date
    Aug 2003
    Location
    Provo, UT, USA
    Posts
    3
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you give an example of the many-to-many relationship that you're trying to structurally create?

    i bet there's three tables involved, the middle one being the "junction" table that machado mentioned

    if not, i would like to see this many-to-many relationship myself

    please give examples of rows with key values, even if they aren't defined as actual primary and foreign keys, i would like to see the values and see how they relate as values

    are you familiar with DDL?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •