Many-to-many relationships via subform

[John Hackert]

Mainly because of fear of corruption of an .mdb in a multiuser
environment, I have been experimenting with the format of an .adp front
end to a SQL Server back end (using the Express version of SQL Server
2005).

Although it seems that the ADP option is perhaps going to be phased
out, I don't see many practical alternatives for a front end to the
otherwise esteemed SQL Server.

I've encountered a problem that I think relates to the inability (as I
understand it) of SQL Server to update data in more than one table in a
multiple-table join. Although I've learned the technique of using
parameterized stored procedures as the record source of simple pop-up
forms, for example, I cannot seem to resolve this specific problem:
how to handle many-to-many relationships with a form/subform.

This schematic has been working well in the .mdb version I've upsized:

tblParent:
ParentID
(other columns)

tblChild:
ChildID
(other columns)

and a resolver table,

tblParentChild:
ParentChildID
ParentID
ChildID

where ParentID and ChildID are a combined primary key.

The "Parent" form has a "Child" subform, associated by Link Master/Link
Child fields on ParentID. The subform's recordsource is a query in
this format:

SELECT tblParentChild.ParentID, tblParentChild.ChildID, tblChild.(other
columns)
FROM tblChild
INNER JOIN tblParentChild ON tblChild.ChildID = tblParentChild.ChildID;


As I've tried to mirror this in the ADP/SQL Server setup, it seems that
during the attempt to add a new record in the subform, the ParentID is
not registered. I receive the error:

"Cannot insert the value NULL into column 'ParentID',
table 'DatabaseName.dbo.tblParentChild'; column does not allow nulls.
INSERT fails."

Any advice would be greatly appreciated.