Access 2003 - Create and delete Many-To-Many associations

I need to develop a front end to a MSSQL database just to modify a few tables. I decided to use Access 2003 simply because of time restraints. I used Linked Tables over ODBC to get them into Access, I'm designing the forms but I'm having problems creating an interface to allow users to create and delete new association between entities.

My Database structure is:

product
# productcode
- name

product_part
* productcode
* partnumber
- position

part
# partnumber
- comment

There is a many-to-many relationship between product and part (a product can have many parts and a part can belong to many products) except I can't find any easy way to allow a user to just associate a new part to product, only view the existing ones.

I've defined the relationships in Access except the options for cardinality and referential integrity are greyed out, I'm assuming this is because they're linked tables? Not sure if this would affect anything.

I created a form for product with an embedded subform which lists all the associated parts and their position (position is an attribute of the relationship since it's contextual but I can spin this out into it's own table if it'll make things easier).

Basically I need to make an user interface mechanism which will associate a selected part from a list to the shown product or any other way to create new and delete existing associations flexibly. I would have thought Access would have something in some wizard somewhere to do this, but if it does I can't find it.

Any help would be appreciated.

---

Judging on what noted so far, then this should be a simple matter to have the main form based on your topmost table (product). The continues sub form should then be based on ONLY the product part table.

If you think about this, the third table is really only a lookup table there for your convenience to allow you to not have to type in manually type in the part number.

So, base the child sub form as a continuous form, and make that column for part number a combo box that looks up the part numbers from the third table (part). So this combo boss can search and display by description, but will in fact automatically store the part number in that colum for you.

So while there's no need for any types of wizards, you certainly do not have to write any type of code whatsoever. Just ensure that the master child link settings for the sub form are set up correctly, and access will thus insert and maintain The product code columns used to link back to the main product table. You can most certainly use the combo box wizard to create the combo box in the continuous sub form that you're going to use to Select what part and set the part number column from the parts table.

The result will be a form that allows you to add new part assemblies or edit existing. While access will maintain the product code column for you, if you delete a main record, you'll need to have setup referential integrity and cascade deletes on the back end database part. So as you correctly note, all the integrity features will be set up in the database back end, not in the access front end part.